Règle du préfixe des index composés et principe ESR

A compound index on multiple fields can serve a wide range of queries — but only if the fields appear in the right order. MongoDB can use a compound index to satisfy a query only if the query's filter matches a prefix of the index. The ordering of fields in the index definition directly controls which queries benefit from it.

A compound index { a: 1, b: 1, c: 1 } can be used by queries that filter on: { a }, { a, b }, or { a, b, c }. These are the prefixes. A query filtering only on { b } or { b, c } cannot use this index — it would do a collection scan. The index is like a phone book sorted by last name, then first name: you can look up by last name alone or by last + first, but not by first name alone.

The ESR principle is a field-ordering rule for compound indexes: put Equality fields first, Sort fields second, and Range fields last. This ordering maximises the portion of the query the index can satisfy and minimises the number of index entries that must be examined. ESR is the most important compound index design rule in MongoDB.

Equality predicates (field: value or $eq) narrow the index scan to a single, fixed value. Placing them first dramatically reduces the number of index entries the query planner needs to consider. Once equality has pinpointed the exact bucket of matching keys, the sort and range operations work on a much smaller dataset.

Placing sort fields before range fields allows MongoDB to use the index to satisfy the sort without a blocking in-memory sort. If range fields come before sort fields, MongoDB must scan all matching range documents, sort them in memory, then return results — adding CPU and memory overhead. With sort fields second, results emerge from the index already in the correct order.

Range predicates like $gt, $lt, $gte, $lte, $in, and regex span a contiguous portion of the index. By placing them last, MongoDB first narrows results with equality and delivers them in sort order, then applies the range check as a final filter. The index scan stays efficient because range does not break the sorted traversal order set by the sort fields.

$in with a small list of values behaves more like equality and can be placed first. When the list is large, it acts more like a range and should go later in the index. A useful rule: if the $in list has fewer than 10–20 values and you query it frequently, treat it as equality (first). For large, dynamic lists, treat it as range (last).

Always verify your index design with explain('executionStats'). Look for: IXSCAN (index scan) — good. COLLSCAN (collection scan) — missing index. SORT stage present — in-memory sort, index field order might be wrong. keysExamined / nReturned should be close to 1 for an optimal compound index.

Thanks to the prefix rule, a single well-designed compound index can replace several single-field indexes. An index on { a: 1, b: 1, c: 1 } makes separate indexes on { a: 1 } and { a: 1, b: 1 } redundant. Fewer indexes means less write overhead and less memory pressure — important for write-heavy workloads where index maintenance adds latency to every insert, update, and delete.

Beyond ESR, consider selectivity — how many documents share the same value. Put the most selective equality field first (fewest duplicates). For example, userId is more selective than status. Placing the more selective field first narrows the scan faster. When multiple equality fields exist, order them most-selective to least-selective for maximum performance.

When designing a compound index, start by listing your top query's filter conditions and sort, then classify each field as E (equality), S (sort), or R (range). Build the index in that order. Run explain('executionStats') to confirm you see an IXSCAN with no SORT stage and a keysExamined/nReturned ratio near 1. Revisit the index whenever query patterns change.

Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.

In this lesson you learned: a compound index can only be used when the query matches a prefix of the index fields, the ESR principle dictates ordering fields as Equality, Sort, Range for maximum query coverage, and placing sort fields before range fields eliminates costly in-memory sort stages. Next up we compare index intersection versus compound indexes.