Learn the ESR rule for compound indexes, covering indexes, index selectivity, and common indexing mistakes that hurt performance.

Indexing Strategies

Creating an index is easy. Creating the right index is harder. A poorly designed index can be almost as bad as no index at all — it takes up space, slows down writes, and sometimes does not even get used by the query planner.

This file covers the strategies that turn good indexes into great ones.

The ESR Rule

The ESR rule tells you the correct order of fields in a compound index. It stands for:

E — Equality fields first
S — Sort fields second
R — Range fields last

This is the single most important rule for compound index design. Follow it and your compound indexes will almost always perform well.

Why field order matters

A compound index on { grade: 1, name: 1, age: 1 } is completely different from { age: 1, name: 1, grade: 1 }. Same fields, different order — completely different performance characteristics.

MongoDB reads compound indexes from left to right. It can use the leftmost fields efficiently, but once it hits a range field, it cannot use subsequent fields as efficiently. This is why the order matters so much.

Equality fields first (E)

Equality fields are fields where your query uses an exact match — { grade: "10th" } or { enrolled: true }. These narrow down the result set the most and should come first.

// Query — two equality conditions
db.students.find({ grade: "10th", enrolled: true })

// Correct — equality fields first
db.students.createIndex({ grade: 1, enrolled: 1 })

Sort fields second (S)

Sort fields come after equality fields. Putting sort fields in the index means MongoDB can use the index for sorting — avoiding the slow in-memory sort.

// Query — equality + sort
db.students.find({ grade: "10th", enrolled: true }).sort({ name: 1 })

// Correct — equality first, sort second
db.students.createIndex({ grade: 1, enrolled: 1, name: 1 })

With this index, MongoDB finds 10th grade enrolled students using the first two fields, then walks through them in name order using the third field. No in-memory sort needed.

Range fields last (R)

Range fields use operators like $gt, $lt, $gte, $lte, $in. These match multiple values and should come after equality and sort fields.

// Query — equality + sort + range
db.students.find({
  grade: "10th",      // equality
  enrolled: true,     // equality
  age: { $gte: 15, $lte: 17 }  // range
}).sort({ name: 1 })  // sort

// Correct ESR order
db.students.createIndex({ grade: 1, enrolled: 1, name: 1, age: 1 })
//                         E           E           S       R

A complete ESR example

Let's apply ESR to a real school system query:

// Query — find enrolled 10th grade students
// aged between 15 and 17
// sorted by name
db.students.find({
  grade: "10th",                    // E — equality
  enrolled: true,                   // E — equality
  age: { $gte: 15, $lte: 17 }      // R — range
}).sort({ name: 1 })                // S — sort

Applying ESR — Equality, Sort, Range:

// Wrong — range before sort
db.students.createIndex({ grade: 1, enrolled: 1, age: 1, name: 1 })

// Correct — ESR order
db.students.createIndex({ grade: 1, enrolled: 1, name: 1, age: 1 })
//                         E           E           S       R

Let's verify with explain():

// Check the wrong index
db.students
  .find({ grade: "10th", enrolled: true, age: { $gte: 15, $lte: 17 } })
  .sort({ name: 1 })
  .hint({ grade: 1, enrolled: 1, age: 1, name: 1 })
  .explain("executionStats")
// Look for: stage: "SORT" — in-memory sort needed

// Check the correct ESR index
db.students
  .find({ grade: "10th", enrolled: true, age: { $gte: 15, $lte: 17 } })
  .sort({ name: 1 })
  .hint({ grade: 1, enrolled: 1, name: 1, age: 1 })
  .explain("executionStats")
// Look for: no SORT stage — index handles the sort

The ESR rule is a guideline, not an absolute law. Sometimes the query planner finds a good plan even with a different field order. But when you are unsure, ESR gives you the right starting point almost every time.

Covering Indexes

A covering index is an index that contains all the fields a query needs — both the filter fields and the projection fields. When a covering index exists, MongoDB can answer the query entirely from the index without ever touching the actual documents.

This is the fastest possible query — MongoDB never reads any documents at all.

How it works

Normally, a query has two steps:

Use the index to find matching document pointers
Fetch the actual documents from disk

With a covering index, step 2 disappears — all the data the query needs is already in the index.

Example — Student list page

Our school app has a student list page that shows name, grade, and enrolled status for all enrolled 10th grade students:

// The query
db.students.find(
  { grade: "10th", enrolled: true },      // filter
  { name: 1, grade: 1, enrolled: 1, _id: 0 }  // projection
)

This query needs four fields — grade, enrolled, name, and it excludes _id. Create an index that covers all of them:

db.students.createIndex({ grade: 1, enrolled: 1, name: 1 })

The index contains grade, enrolled, and name — exactly what the query needs. MongoDB can return results straight from the index.

Verify with explain():

db.students
  .find(
    { grade: "10th", enrolled: true },
    { name: 1, grade: 1, enrolled: 1, _id: 0 }
  )
  .explain("executionStats")

Look for this in the output:

{
  stage: "PROJECTION_COVERED",   // covered by index
  inputStage: {
    stage: "IXSCAN",
    // no FETCH stage — documents never touched
  }
}

If you see PROJECTION_COVERED with no FETCH stage — the index is covering the query.

What breaks a covering index

Including _id in the projection — _id is not in the index, so including it forces a document fetch. Always exclude _id when you want a covering index and do not need _id.

// Breaks covering — _id forces document fetch
db.students.find(
  { grade: "10th", enrolled: true },
  { name: 1, grade: 1, enrolled: 1 }  // _id included by default
)

// Covering — explicitly exclude _id
db.students.find(
  { grade: "10th", enrolled: true },
  { name: 1, grade: 1, enrolled: 1, _id: 0 }
)

Requesting a field not in the index — if your projection includes a field that is not in the index, MongoDB must fetch the document.

// Breaks covering — age is not in the index
db.students.find(
  { grade: "10th", enrolled: true },
  { name: 1, grade: 1, enrolled: 1, age: 1, _id: 0 }
)

// Fix — add age to the index
db.students.createIndex({ grade: 1, enrolled: 1, name: 1, age: 1 })

Index Selectivity

Selectivity measures how well an index narrows down the result set. A highly selective index matches very few documents. A low selectivity index matches most documents.

High selectivity = good index Low selectivity = poor index

Example — high vs low selectivity

// Low selectivity — enrolled is true for 95% of students
// The index matches almost every document — barely helps
db.students.createIndex({ enrolled: 1 })

// High selectivity — email is unique per student
// The index matches exactly one document — very fast
db.students.createIndex({ email: 1 })

// Medium selectivity — grade has 4 values
// Each grade is about 25% of students — reasonable
db.students.createIndex({ grade: 1 })

Check selectivity manually

const totalStudents = db.students.countDocuments();

// How many match enrolled: true?
const enrolledCount = db.students.countDocuments({ enrolled: true });
const enrolledSelectivity = enrolledCount / totalStudents;
console.log(`Enrolled selectivity: ${enrolledSelectivity}`); // 0.95 — poor

// How many match grade: "10th"?
const tenthCount = db.students.countDocuments({ grade: "10th" });
const gradeSelectivity = tenthCount / totalStudents;
console.log(`Grade selectivity: ${gradeSelectivity}`); // 0.25 — reasonable

A selectivity close to 1 means almost all documents match — the index barely helps. A selectivity close to 0 means very few documents match — the index is very effective.

When low selectivity indexes help

A low selectivity index on its own is weak. But combined with a high selectivity field in a compound index, it can still be useful:

// enrolled alone is weak (95% match)
// But grade + enrolled together is more selective
db.students.createIndex({ grade: 1, enrolled: 1 })
// grade: "10th" = 25%, grade: "10th" + enrolled: true = maybe 23%
// Still reasonable — and enables covering indexes

Index Intersection vs Compound Index

MongoDB can sometimes use two separate indexes on the same query — called index intersection. But this is generally slower than a single well-designed compound index.

// Two separate indexes
db.students.createIndex({ grade: 1 })
db.students.createIndex({ enrolled: 1 })

// MongoDB might use both via index intersection
db.students.find({ grade: "10th", enrolled: true })

// One compound index — better
db.students.createIndex({ grade: 1, enrolled: 1 })

Index intersection is a fallback — not a strategy. Always prefer a compound index over two single field indexes when you frequently query both fields together.

How Many Indexes is Too Many

There is no hard limit, but every index has a cost:

Storage — each index takes disk space
Write overhead — every insert, update, and delete must update all indexes
RAM — MongoDB tries to keep indexes in memory

A general guideline:

3–5 indexes per collection is reasonable
10+ indexes per collection is a warning sign

Find unused indexes

MongoDB tracks index usage statistics. Find indexes that are never used:

db.students.aggregate([
  { $indexStats: {} },
  {
    $project: {
      name: 1,
      "accesses.ops": 1,
      "accesses.since": 1
    }
  }
])

Output:

[
  { name: "_id_",              accesses: { ops: 5420, since: ISODate("...") } },
  { name: "name_1",            accesses: { ops: 892,  since: ISODate("...") } },
  { name: "grade_1_enrolled_1",accesses: { ops: 1205, since: ISODate("...") } },
  { name: "phone_1",           accesses: { ops: 0,    since: ISODate("...") } }
]

phone_1 has ops: 0 — it has never been used. Drop it:

db.students.dropIndex("phone_1")

Index stats reset when MongoDB restarts. Let your app run for a full week before judging which indexes are unused — some indexes are only hit by weekly reports or batch jobs.

School System — Final Index Strategy

Here is the complete, optimized index set for our school system applying everything in this file:

// students collection
// ─────────────────────────────────────────

// Student list page — covering index (ESR)
// Query: { grade, enrolled } sort: { name }
// Projection: name, grade, enrolled, _id: 0
db.students.createIndex({ grade: 1, enrolled: 1, name: 1 })

// Student search by name
db.students.createIndex({ name: 1 })

// Age range queries — range field last (ESR)
// Query: { grade, enrolled, age: { $gte, $lte } } sort: { name }
db.students.createIndex({ grade: 1, enrolled: 1, name: 1, age: 1 })

// Unique email
db.students.createIndex({ email: 1 }, { unique: true, sparse: true })

// Enrollment date — for sorting recently enrolled
db.students.createIndex({ enrollmentDate: -1 })

// City queries — partial index for enrolled students only
db.students.createIndex(
  { "address.city": 1 },
  { partialFilterExpression: { enrolled: true } }
)

// courses collection
// ─────────────────────────────────────────

// Unique course code
db.courses.createIndex({ code: 1 }, { unique: true })

// Course list by grade — covering index
// Query: { grade } projection: title, code, totalStudents
db.courses.createIndex({ grade: 1, title: 1, code: 1, totalStudents: 1 })

// Teacher lookup on courses
db.courses.createIndex({ teacherId: 1 })

// teachers collection
// ─────────────────────────────────────────

// Unique email
db.teachers.createIndex({ email: 1 }, { unique: true })

// Active teachers by subject — partial + covering
db.teachers.createIndex(
  { subject: 1, name: 1 },
  { partialFilterExpression: { active: true } }
)

// enrollments collection
// ─────────────────────────────────────────

// Find all courses for a student — ESR
// Query: { studentId, status } sort: { enrolledAt }
db.enrollments.createIndex({ studentId: 1, status: 1, enrolledAt: -1 })

// Find all students in a course
db.enrollments.createIndex({ courseId: 1, status: 1 })

// attendance collection
// ─────────────────────────────────────────

// Find attendance for a student in a date range — ESR
// Query: { studentId } range: { date } — bucket pattern so month is equality
db.attendance.createIndex({ studentId: 1, month: 1 })

Quick Reference

Strategy	Rule	Example
ESR rule	Equality → Sort → Range	`{ grade: 1, name: 1, age: 1 }`
Covering index	Include all filter + projection fields	Avoid FETCH stage entirely
Selectivity	High selectivity fields first	`email` before `enrolled`
Index intersection	Avoid — use compound instead	One index beats two
Unused indexes	Drop them — they slow writes	Check `$indexStats`

The best index strategy is built around your actual queries — not your data structure. Before designing any index, write down the three or four queries that run most often in your app. Apply ESR to each one. Check if they can share a compound index. Build covering indexes for your most frequent read queries. That process produces better indexes than any general rule.

Indexing Strategies

On this page