Compound indexes and the ESR rule

conceptThe concept

Open the interactive lesson to use this diagram.

A compound index sorts by the first field, then by the second within equal first values, and so on. For a query with equality, sort and range predicates, the best order is:

1. Equality fields first: they pin the scan to one contiguous slice.
2. Sort fields next: within that slice, keys are already in sort order, so no SORT stage.
3. Range fields last: the range becomes a filter on keys while walking in sort order.

Putting the range before the sort field forces a blocking sort, because keys are ordered by the range field first.

Walking the index

Open the interactive lesson to use this diagram.

Prefixes: { a: 1, b: 1, c: 1 } also serves queries on a and on a, b, but not on b alone. Covered queries read only the index: all filter and projected fields are in the index and _id is excluded (or indexed).

For sorts on multiple fields the direction must match the index or be its exact reverse: { a: 1, b: -1 } supports sort({ a: 1, b: -1 }) and sort({ a: -1, b: 1 }) but not sort({ a: 1, b: 1 }).

ESR lab

Pick an index order and run the same query against a private copy of the orders collection. The lab reports keys and documents examined and whether a SORT stage was needed.

Open the interactive lesson to use this diagram.

whyWhy it matters

The ESR rule turns many slow queries into fast ones without adding more indexes. It is also a favourite interview topic because it tests whether you understand what an index physically is.

Use caseReal-world scenario

"Paid orders over 500, newest first, 20 per page": equality on status, sort on createdAt, range on total. The ESR index { status: 1, createdAt: -1, total: 1 } returns page one after examining a few dozen keys with no SORT stage. The tempting { status: 1, total: 1, createdAt: -1 } scans every paid order above 500 and sorts them in memory.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.createIndex({ status: 1, total: 1, createdAt: -1 })
db.orders.find({ status: "paid", total: { $gte: 500 } }).sort({ createdAt: -1 }).limit(20).hint({ status: 1, total: 1, createdAt: -1 }).explain("executionStats")

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })
db.orders.find({ status: "paid", total: { $gte: 500 } }).sort({ createdAt: -1 }).limit(20).hint({ status: 1, createdAt: -1, total: 1 }).explain("executionStats")

db.customers.createIndex({ tier: 1, "address.country": 1, name: 1 })
db.customers.find({ tier: "gold", "address.country": "India" }, { _id: 0, name: 1 }).explain("executionStats")

db.customers.find({ tier: "gold" }).explain().queryPlanner.winningPlan
db.customers.find({ "address.country": "India" }).explain().queryPlanner.winningPlan

internalsUnder the hood

For an equality on the first field and a range on the second, index bounds are one interval per field. With $in on an early field the planner can still avoid a sort by merging the sorted streams for each value (SORT_MERGE), or by exploding into point scans. ESR is a guideline: when the range is extremely selective (returns a handful of documents), E-R-S may examine fewer keys and the small in-memory sort is cheap. Always confirm with explain.

Avoid / preferDo this, not that

db.orders.createIndex({ total: 1, status: 1, createdAt: -1 })
db.orders.find({ status: "paid", total: { $gt: 100 } }).sort({ createdAt: -1 })

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

db.orders.createIndex({ status: 1, createdAt: -1, total: -1 })
db.orders.find({ status: "paid" }).sort({ createdAt: -1, total: 1 })

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

PitfallsCommon mistakes

• Ordering compound fields by "selectivity" alone instead of by how the query uses them (equality, sort, range).
• Creating { a: 1 } next to { a: 1, b: 1 }: the single-field index is redundant (unless it is unique or sparse with different semantics).
• Expecting { a: 1, b: 1 } to help a query on b alone.
• Assuming a covered query when the projection includes _id or a field not in the index, or when the field is multikey.

LimitsWhen not to use it

ESR is a starting point, not a law. When the range is extremely selective (the last five minutes of a year of data), putting the range before the sort can examine fewer keys, and sorting the few results in memory is cheap. Check both with explain("executionStats"). Also resist one compound index per query shape: consolidate where prefixes overlap.

SecuritySecurity notes

Index design assumes known query shapes. Do not let clients pick arbitrary filter or sort fields: one unindexed sort field is enough to turn an API into a collection-scan generator. Since 8.0, setQuerySettings with reject: true can block a runaway query shape without an application deploy.

ChallengeTry it yourself

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })
const e = db.orders.find({ status: "paid", total: { $gt: 100 } }).sort({ createdAt: -1 }).explain("executionStats")
const stages = JSON.stringify(e.queryPlanner.winningPlan).match(/"stage":"[A-Z_]+"/g)
printjson({ stages, keys: e.executionStats.totalKeysExamined, returned: e.executionStats.nReturned })

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.