MongoDB Compound Indexes: Getting Field Order Right with the ESR Rule

A compound index can contain all the right fields and still be the wrong index. The field order decides whether MongoDB can use it to narrow the scan, to return documents already sorted, or both. My first rule of thumb is the ESR rule from the MongoDB docs: Equality fields first, then Sort fields, then Range fields.

The query

The latest 20 delivered orders over 100 for one customer:

db.orders
  .find({ customerId: 42, status: 'DELIVERED', total: { $gte: 100 } })
  .sort({ createdAt: -1 })
  .limit(20)

customerId and status are equality matches, createdAt is the sort, and total is a range. The index most people create first simply follows the query from left to right:

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

Reading explain()

explain('executionStats') runs the query and reports what the winning plan did. What to look for:

• Stages: IXSCAN means an index was used, COLLSCAN means the collection was scanned document by document. A SORT stage means the results were sorted in memory (a blocking sort) instead of being read in index order.
• nReturned, totalKeysExamined, totalDocsExamined: ideally, all three are close. Far more keys than results means the index isn't narrowing the scan; far more documents than results means filtering happens after the fetch.

On a synthetic collection of 200k orders, the index above gives this (plan simplified, parent stage first):

FETCH <- SORT { createdAt: -1 } limit 20 <- IXSCAN { customerId, status, total, createdAt }
nReturned: 20   totalKeysExamined: 1887   totalDocsExamined: 20

The equality fields do their job, but within this customer's delivered orders the index entries are ordered by total, not by date. MongoDB reads all 1,887 entries with a total of at least 100 and sorts them in memory, only to keep 20. It sorts the index keys before fetching, which is why only 20 documents are examined.

For comparison, without any index the plan was a COLLSCAN with totalDocsExamined: 200000. With only { customerId: 1, status: 1 }, MongoDB fetched all 2,337 delivered orders of this customer to filter on total, then sorted them.

Equality, Sort, Range

Swap the last two fields:

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

LIMIT <- FETCH <- IXSCAN { customerId, status, createdAt, total }
nReturned: 20   totalKeysExamined: 27   totalDocsExamined: 20

No SORT stage. The equality fields narrow the scan to one customer's delivered orders, which this index keeps in createdAt order. total is checked on the index keys as the scan walks, so entries under 100 are skipped without fetching anything, and the scan stops after 20 matches.

Why each position:

• Equality first: exact matches narrow the scan to one contiguous part of the index. Their order among themselves doesn't matter for this query.
• Sort next: an index can return results ordered by a later field only if the query has equality conditions on all the fields before it.
• Range last: a range on an earlier field breaks the ordering of every field after it. That's what forced the in-memory sort above.

One 6.0 detail: a blocking sort that needs more than 100 MB no longer fails by default. It spills to disk (allowDiskUseByDefault), which is friendlier, but a missing sort index now shows up as slow queries rather than errors.

When ESR bends

• Very selective ranges. With total: { $gte: 499 } on the same data, the ESR index examined 2,338 keys to return a single order, while the E-R-S index examined one key and sorted one document. ESR trades extra key scanning for skipping the sort. When the range throws away most keys, sorting a handful of documents is cheaper. Which one wins depends on your data, so compare them with hint().
• Operators that look like equality but aren't. $ne, $nin and $regex are range operators. $in is equality on its own, but with a sort it can behave like a range. The planner can merge a short $in list in index order (a SORT_MERGE stage), but don't count on that for long lists.

Takeaways

• Order compound index fields Equality, Sort, Range, then verify with explain('executionStats').
• Compare totalKeysExamined and totalDocsExamined with nReturned, and look for SORT and COLLSCAN stages.
• A very selective range can justify E-R-S. Measure both orders with hint().
• Check the operators: $ne, $nin and $regex are ranges, not equality.