MongoDB Compound Indexes and the ESR Rule
On this page
How compound indexes work: the prefix rule
A compound index sorts entries by the first field, then by the second within each first-field value, and so on, like a phone book sorted by last name, then first name. That ordering gives two properties.
The prefix rule. An index on { customerId: 1, status: 1, placedAt: -1 } also serves queries on its prefixes: { customerId } and { customerId, status }. It doesn't serve a query on status alone, just as a phone book doesn't help you find everyone named "Sam".
Sorting for free. If the index order matches the query's sort, MongoDB reads entries already in order and skips the sort step entirely.
The ESR rule: equality, sort, range
For a query with equality conditions, a sort and a range, order the index fields:
- Equality fields first (
status: "paid"), - then the Sort field (
total), - then Range fields (
createdAt >= ...).
Query: paid orders since June, top 10 by total.
db.orders.find({ status: "paid", createdAt: { $gte: ISODate("2026-06-01") } })
.sort({ total: -1 }).limit(10)On 200,000 test orders in MongoDB 9, two indexes with the same fields in different orders:
| Index | Keys examined | Docs examined | Sort | Time |
|---|---|---|---|---|
{ status: 1, createdAt: 1, total: -1 } (E, R, S) | 30,128 | 10 | In memory | 12 ms |
{ status: 1, total: -1, createdAt: 1 } (E, S, R) | 11 | 10 | None: index order | 2 ms |
With range before sort, MongoDB had to collect every matching key from June onwards and sort them in memory to find the top 10. With sort before range, it walks the index in total order, checks the date on each entry, and stops after 10 matches. As data grows, the first gets steadily worse; the second stays flat. Large in-memory sorts can also hit memory limits and fail or spill to disk.
ESR is a guideline, not a law: if the range condition is extremely selective (it matches almost nothing), putting it before the sort can win. explain tells you which.
Index selectivity and partial indexes
Selectivity is how much a condition narrows the result. orderId is highly selective (one document); status with three values isn't (a third of the collection each). Among equality fields, order matters less than people think, because MongoDB can seek on all of them. What matters most is that the index puts the selective conditions where they can be used, follows ESR, and matches your sort.
Low-selectivity fields alone make poor indexes: an index on status alone still returns a third of the collection. Partial indexes can help when you only ever query a small subset, such as { status: "open" }:
db.orders.createIndex({ createdAt: 1 }, { partialFilterExpression: { status: "open" } })How to design indexes for an application
- List the important queries with their filters and sorts.
- Design compound indexes using ESR, and let one index serve several queries through its prefixes.
- Check each query with
explain("executionStats"): keys and docs examined close tonReturned, and noSORTstage where you expect index order. - Remove indexes that are prefixes of others (
{ customerId: 1 }is redundant next to{ customerId: 1, status: 1 }).
Check yourself
The query on a and the query on a and b, because they use prefixes of the index. A query on b and c without a can't seek into the index, since entries are ordered by a first, so at best it scans the whole index.
The index then returns entries in sort order, so MongoDB can stop after enough matches instead of collecting every range match and sorting in memory.
Status has only a few distinct values, so each one still matches a large fraction of the collection, and MongoDB has to fetch most documents anyway. Combine it with more selective fields in a compound index, or use a partial index for a rare status.
Indexing only documents that match a filter, such as orders with status: "pending". The index stays small and fast while serving queries for that subset.
For single-field sorts no, but for sorts on several fields it does: { a: 1, b: -1 } supports sorting by a ascending and b descending, or the exact reverse, not both ascending.