TopolearnMost production queries filter on several fields and sort, and field order decides whether an index helps.
A compound index such as { status: 1, createdAt: -1 } is sorted by status first and, within each status, by createdAt. Like a phone book sorted by last then first name, it supports queries on any prefix: { status } alone or { status, createdAt }, but not { createdAt } alone.
Order fields with the ESR rule:
- Equality fields first (
status: "paid",userId: 42), - then Sort fields (
createdAt: -1), - then Range fields (
total: { $gt: 100 },$inwith many values behaves like a range for sorting).
With equality fields first, all matching entries are contiguous and already in sort order, so MongoDB avoids an in-memory SORT stage. Putting a range field before the sort field breaks that ordering.
Direction matters only between fields: { a: 1, b: -1 } can serve sort { a: 1, b: -1 } or its reverse { a: -1, b: 1 }, but not { a: 1, b: 1 }.
A query is covered when the filter and projection use only indexed fields (and exclude _id unless it's indexed): the answer comes straight from the index without fetching documents, shown as no FETCH stage in explain.
One well-designed compound index often replaces several single-field ones, because its prefixes serve those queries too.
Example
db.orders.createIndex({ customerId: 1, createdAt: -1, total: 1 })
// E (customerId) + S (createdAt) + R (total): one IXSCAN, no SORT stage
db.orders.find({ customerId: 42, total: { $gt: 100 } }).sort({ createdAt: -1 })
// prefix use: also served by the same index
db.orders.find({ customerId: 42 })
// not a prefix: cannot use this index efficiently
db.orders.find({ createdAt: { $gte: ISODate("2026-01-01") } })
// covered query: only indexed fields, _id excluded, no FETCH stage
db.orders.find({ customerId: 42 }, { _id: 0, createdAt: 1, total: 1 })Remember
- Compound indexes serve queries on a prefix of their fields
- Order fields Equality, Sort, Range
- Direction matters only relative to other fields in the index
- Covered queries are answered from the index alone
Watch out for
- Putting the range field before the sort field, which forces an in-memory sort.