The ESR Rule: Getting Compound Index Field Order Right in MongoDB

Two indexes on the same three fields, in different orders, are not two ways of doing the same thing. One serves your query from the index alone. The other makes MongoDB pull documents into memory and sort them there, and falls over at 32MB.

The rule that tells them apart is ESR: Equality, Sort, Range.

Key takeaways#

• Order compound index keys equality fields first, then sort fields, then range fields.
• A range predicate placed before a sort field forces an in-memory sort — capped at 32MB, after which the query fails outright.
• A compound index serves any prefix of its keys, so {a, b, c} also covers {a} and {a, b}, but never {b} alone.
• Verify with explain(): you want IXSCAN with no SORT stage.
• Index direction only matters when sorting on more than one field.

Why does field order matter at all?#

Because a compound index is a single sorted structure, ordered by the first key, then by the second within each first-key value, and so on. It is a phone book sorted by surname then first name: finding every "Nguyen, Anh" is instant, finding every "Anh" regardless of surname means reading the whole book.

That structure has one property everything else follows from — entries are contiguous only while the keys before them are fixed to a single value. An equality match on the first key confines you to one contiguous block. A range match on it spreads you across many.

The rule#

For an index { a: 1, b: 1, c: 1 } serving a query, place fields in this order:

1. Equality — fields matched against exactly one value (status: 'active', $in with a small list).
2. Sort — fields the query sorts on, in sort order.
3. Range — fields matched with $gt, $lt, $gte, $lte, $ne, or a regex that is not left-anchored.

Take a query that lists a customer's recent orders above a value:

db.orders.find({ customerId: 'c_8812', total: { $gt: 100 } }).sort({ createdAt: -1 });

customerId is equality, createdAt is the sort, total is a range. So:

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

Now MongoDB seeks to the customerId block, walks it in createdAt order — already sorted, nothing to do — and filters on total as it goes.

What goes wrong with the other order#

Put the range before the sort and the index looks reasonable:

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

Within customerId: 'c_8812', entries are ordered by total. The query wants every total above 100, which is many different total values, and createdAt is only sorted within each one. The index cannot hand back rows in createdAt order, so MongoDB gathers every match and sorts them in memory.

That shows up as a SORT stage in the explain plan, and it has a hard ceiling: an in-memory sort that exceeds 32MB fails with Sort exceeded memory limit, unless the query permits a spill to disk. The failure arrives with data volume, so it passes in development and breaks in production.

Proving it with explain#

Do not reason about this from the shape of the query. Ask the server:

db.orders
  .find({ customerId: 'c_8812', total: { $gt: 100 } })
  .sort({ createdAt: -1 })
  .explain('executionStats');

Read three things from the output:

• winningPlan.stage — IXSCAN means an index was used, COLLSCAN means it was not.
• A SORT stage anywhere in the plan — this is the tell. Its presence means the index did not supply the order and MongoDB sorted in memory.
• totalKeysExamined versus nReturned — close together is a selective index. Ten thousand keys examined to return ten documents means the index is being scanned, not seeked.

A well-ordered index gives you IXSCAN, no SORT, and totalKeysExamined near nReturned. Anything else is worth another look at the field order.

The prefix rule#

A compound index can serve any query that uses a left-hand prefix of its keys. { customerId: 1, createdAt: -1, total: 1 } also serves:

• { customerId } alone
• { customerId, createdAt }

It does not serve a query on createdAt alone, or on total alone. There is no way into the middle of the structure without fixing what comes before it.

This is the single biggest lever on index count. Three separate indexes on {customerId}, {customerId, createdAt} and {customerId, createdAt, total} are redundant — the third covers all three. Every index you drop is write amplification you stop paying, because each one has to be updated on every insert, update and delete.

When ESR is the wrong answer#

ESR optimises for using the index to satisfy the sort. Two situations beat it.

A covered query. If the index contains every field the query returns, MongoDB never touches the documents at all. Appending a projected field to the end of the index — after the range field, where it does nothing for ordering — can turn an IXSCAN plus fetch into an index-only scan. Check for totalDocsExamined: 0.

A very selective range. ESR assumes the equality fields narrow the search enough that the sort dominates. When a range field is dramatically more selective — a timestamp window holding twelve documents out of fifty million — putting it earlier and eating an in-memory sort of twelve documents is faster. The 32MB ceiling is not a risk at that size.

Both are exceptions you measure rather than assume. Start with ESR, read the explain plan, and depart from it only with numbers in hand.

Applying it#

The workflow that catches this before production, rather than after:

1. Write the query first. Index design follows access patterns, never the other way round.
2. Classify each predicate as equality, sort, or range.
3. Build the index in that order.
4. Run explain('executionStats') against production-shaped data volume.
5. Confirm IXSCAN, no SORT, and totalKeysExamined close to nReturned.
6. Drop any index that is now a prefix of another.

Step four is the one that gets skipped. An explain plan against a hundred seeded documents will happily report a fast in-memory sort, right up until the collection has a million.

For the mechanics of creating these indexes safely on a live collection, see how to create an index in MongoDB.

Checking the planner agreed with you means reading explain() output, which nests several levels deep. The JSON Formatter makes the winning plan easy to find.