Compound Indexes and the ESR Rule
The genre page asks for classic books under 10, oldest first. Three fields are involved: one tested for equality, one for a range, one for sorting. A single-field index helps with one of them. A compound index can serve all three, but only if the fields are in the right order.
The rule
Equality, then Sort, then Range; queries use the index from the left.
db.books.createIndex({ genres: 1, year: 1, price: 1 })A compound index is sorted by the first field, then by the second within each first value, and so on, like a phone book sorted by surname, then first name.
Prefix rule: the index serves queries on any leading prefix of its fields.
| Query filters on | Uses { genres, year, price }? |
|---|---|
genres |
yes |
genres and year |
yes |
genres, year and price |
yes |
year only |
not efficiently: year is not a prefix |
Sort direction: an index { a: 1, b: -1 } serves sort({ a: 1, b: -1 }) and its exact reverse sort({ a: -1, b: 1 }), but not sort({ a: 1, b: 1 }).
ESR rule for choosing the order: Equality fields first, then Sort fields, then Range fields.
The shortcut
Label every field in the query E, S or R, then write the index in that order.
Label the fields
genres: "classic"is E.sort({ year: 1 })is S.price: { $lt: 10 }is R.
The trap: putting the range before the sort, { genres: 1, price: 1, year: 1 }. The classic block is then ordered by price, so MongoDB must re-sort by year in memory.
Drills
Answer fast — trust the rule.
Which query can use the index { author: 1, year: 1 } efficiently?
In the ESR rule, what does the R stand for?
Index { stock: 1, price: -1 }. Which sort can it serve without an in-memory sort?
Best index for find({ author: "Jane Austen", year: { $gt: 1800 } }).sort({ price: 1 })?
Remember
- Equality, then Sort, then Range; queries use the index from the left.
- Shortcut: label each field E, S or R and write the index in that order.
- Trap: a range before the sort field forces an in-memory sort.