Mongodb Compound Index Esr Rule

Order fields as Equality → Sort → Range.

• Equality — fields filtered with =
• Sort — fields used in sort()
• Range — fields with <, >, between, or $in over a large set

Why

MongoDB consumes the index left-to-right. Equality fields narrow the scan to a contiguous key range; sort fields let the engine return results without an in-memory sort; range fields expand the scan and must come last — placing them earlier forces a wider scan and may break sort usage.

Steps

1. List the query's predicates; classify each field as equality, sort, or range.
2. Build the index in ESR order. Drop fields that don't participate.
3. Remove dead indexes whose key prefix is covered by the new one (left-prefix rule).
4. Verify with db.<coll>.find(...).explain("executionStats"): want IXSCAN and , with no stage.