What is the ESR rule for ordering the fields of a MongoDB compound index?
answer
- Three letters, three predicate kinds
- One of them must come last, always
- Ordering survives only until a span begins
- Getting it wrong adds a blocking stage to the plan
basics
~20 sESR orders compound-index keys as Equality first, then Sort fields, then Range fields. Equality pins the scan to a narrow contiguous key range, the sort fields then arrive already ordered, and range predicates go last because they destroy ordering for the keys after them.
solid answer
~50 s**E-S-R** is MongoDB's guidance for the field order of a compound index: put fields tested with **equality** first, then the fields the query **sorts** on, then fields tested with a **range** (`$gt`, `$lt`, `$ne`). Equality fields fix a prefix of the key, so the scan starts on one contiguous stretch of the index. Because every key in that stretch shares the same equality values, the next field is stored in order — so the sort fields placed there let the index deliver documents already sorted and the plan needs no blocking `SORT` stage. A range field must come last, because once the scan spans many values of a field, the fields after it are no longer globally ordered. Get the order wrong and `explain()` shows either a `SORT` stage or a huge `totalKeysExamined`, or both.
code
javascript · 3 linesdb.orders.find({ status: "ACTIVE", createdAt: { $gt: cutoff } })
.sort({ updatedAt: -1 })
.limit(20)go deeper
Be able to expand the acronym and state the order: equality fields first, sort fields next, range fields last. Knowing the order is the expected floor at this level.
Explain the mechanism behind each position — equality selects one contiguous key block, the next field is therefore already ordered, and a range destroys the ordering of everything after it.
Show the evidence: point at the SORT stage and the examined counters in explain output, then demonstrate the reordered index removing the blocking sort, and know when measurement overrides the heuristic.
Own index strategy across a workload: which compound indexes exist, how prefixes are shared between query families, and the write cost of an index per query shape versus a slightly worse plan.
## The rule When you build a compound index for a query, order its fields **Equality, Sort, Range**: 1. **Equality** — every field the query pins to a single value (`{ status: "ACTIVE" }`, `{ tenantId: 42 }`, and a small `$in` behaves similarly). 2. **Sort** — the fields in the query's `sort()`, in the same order they appear there. 3. **Range** — fields compared with `$gt`, `$gte`, `$lt`, `$lte`, `$ne`, or a range-shaped `$in`. For `find({ status: "ACTIVE", createdAt: { $gt: cutoff } }).sort({ updatedAt: -1 })` the index is `{ status: 1, updatedAt: -1, createdAt: 1 }`. ## Why the order works A compound index is a single sorted structure whose keys are ordered by the first field, then within equal values of the first by the second, and so on — exactly like sorting a list by several columns. **Equality first.** Fixing a field to one value selects a *contiguous* block of the index. Everything after this point happens inside that block, so the block is as small as the equality predicates can make it. Put a non-equality field first instead, and the scan must range over many values before it can even consider the rest. **Sort next.** Inside a block where the equality fields are constant, the keys are physically ordered by the next field. If that next field is the one you sort by, the index scan emits documents in the required order and the plan is *non-blocking*: it can return the first document immediately and, with a `limit`, stop early. Otherwise the server must materialise every matching document and sort it in a `SORT` stage — which cannot return anything until it has seen everything, and which fails outright if the data exceeds the server's in-memory sort limit for a `find` (unless an index provides the order). **Range last.** A range predicate makes the scan span *many* values of that field. Across that span the following field's values restart from the beginning for each distinct value — so the keys after a range field are not in global order, and any sort placed after a range field cannot be served by the index. Put the range before the sort and you have traded a tight, ordered scan for a blocking sort. ## What the plan looks like when you get it wrong With `{ status: 1, createdAt: 1, updatedAt: -1 }` (range before sort) for the query above, `explain()` shows: - an `IXSCAN` whose `indexBounds` on `createdAt` covers a wide range; - a **`SORT`** stage above it, with a `sortPattern` matching your `sort()`; - `totalKeysExamined` and `totalDocsExamined` far larger than `nReturned`, because every candidate in the range must be read before the sort can order it. Swap to `{ status: 1, updatedAt: -1, createdAt: 1 }` and the `SORT` stage disappears. The scan still evaluates `createdAt` — as a filter on the trailing key rather than as a bound — but it walks the index in `updatedAt` order and can stop as soon as the limit is satisfied. ## Details that matter in an interview - **Sort direction.** An index can serve a sort if the sort keys match the index key directions **exactly or exactly inverted** for all sort fields. `{ a: 1, b: -1 }` serves `sort({ a: 1, b: -1 })` and `sort({ a: -1, b: 1 })`, but not `sort({ a: 1, b: 1 })`. - **The order of equality fields among themselves** does not change selectivity for this query, but it changes which *other* queries the index can serve, because a compound index is usable by queries matching a leading prefix of its keys. Order the equality fields so the prefixes you also need come out useful. - **`$in` is in between.** A short `$in` list is planned much like equality (the scan does one seek per value); a large or range-like `$in` behaves like a range. - **ESR is a heuristic, not a law.** It optimises for eliminating the blocking sort and keeping bounds tight, which is the right default. If a range field is extremely selective and the sort returns few documents anyway, measuring both orders with `explain("executionStats")` may favour the other arrangement. The rule tells you which index to build first; the counters tell you whether to keep it. - **The trailing range field is still useful.** Even when it cannot form a bound, having it in the index means the predicate is evaluated on index keys, so non-matching documents are rejected without a `FETCH`. ## How to answer out loud Say the expansion, give the mechanical reason for each position (contiguous block, then keys already ordered, then ordering destroyed after a span), name the evidence in `explain()` — the presence or absence of a `SORT` stage and the examined-to-returned ratio — and note the sort-direction constraint. That is the complete answer; reciting only "equality, sort, range" reads as memorised lore.
- Why must range predicates come after the sort fields rather than before them?A range makes the scan cover many values of that field, and within each of those values the following key restarts its ordering. So keys after a range field are not globally sorted and cannot supply the query's order — the planner has to insert a blocking SORT stage that reads every match before returning the first document. Placed last, the range field is still evaluated on index keys and filters without a fetch.
- An index is {a: 1, b: -1}. Which sorts can it serve without a SORT stage?sort({a: 1, b: -1}), by walking the index forward, and sort({a: -1, b: 1}), by walking it backward. Any other combination — sort({a: 1, b: 1}) or sort({a: -1, b: -1}) — would need a direction change partway through a single scan, which an index walk cannot do, so the planner adds a blocking sort.
- How do you confirm from explain() that the index actually satisfied the sort?Look for the absence of a SORT stage in the winning plan: if the ordering came from the index, the plan is just IXSCAN plus FETCH (plus LIMIT or SKIP). A SORT node with a sortPattern means the server ordered results itself. Pair that with the examined-to-returned counters, since a blocking sort forces the plan to examine every match before emitting anything.
- Does the relative order of two equality fields in the index matter?Not for this query's selectivity — both are pinned, so either order selects the same contiguous block. It matters for reuse: a compound index also serves queries that match a leading prefix of its keys, so put the equality field you query alone (or with fewer companions) first, and let the index cover a family of queries instead of one.
A compound index is a list sorted by several columns at once. Fix the first column to one value and you are looking at one block of consecutive rows already sorted by the next column; open up a range on that first column instead and the ordering of the later columns is shuffled across the blocks.
saying these in an interview costs you the question
- Orders compound-index fields to match the order in the query text
- Places the range field before the sort field
- Claims field order in a compound index does not matter
- Thinks any index containing the sort field can serve the sort
- Ignores sort direction when the sort has two keys