Catalog My learning Blog
MongoDB Tutorial: Schema Design, Indexing and Sharding · Phase 2 · Lesson 8 · 4 min read

MongoDB Compound Indexes and the ESR Rule

On this page
  1. How compound indexes work: the prefix rule
  2. The ESR rule: equality, sort, range
  3. Index selectivity and partial indexes
  4. How to design indexes for an application
  5. Check yourself

How compound indexes work: the prefix rule

A compound index sorts entries by the first field, then by the second within each first-field value, and so on, like a phone book sorted by last name, then first name. That ordering gives two properties.

The prefix rule. An index on { customerId: 1, status: 1, placedAt: -1 } also serves queries on its prefixes: { customerId } and { customerId, status }. It doesn't serve a query on status alone, just as a phone book doesn't help you find everyone named "Sam".

Sorting for free. If the index order matches the query's sort, MongoDB reads entries already in order and skips the sort step entirely.

The ESR rule: equality, sort, range

For a query with equality conditions, a sort and a range, order the index fields:

  1. Equality fields first (status: "paid"),
  2. then the Sort field (total),
  3. then Range fields (createdAt >= ...).

Query: paid orders since June, top 10 by total.

JavaScript
db.orders.find({ status: "paid", createdAt: { $gte: ISODate("2026-06-01") } })
         .sort({ total: -1 }).limit(10)

On 200,000 test orders in MongoDB 9, two indexes with the same fields in different orders:

IndexKeys examinedDocs examinedSortTime
{ status: 1, createdAt: 1, total: -1 } (E, R, S)30,12810In memory12 ms
{ status: 1, total: -1, createdAt: 1 } (E, S, R)1110None: index order2 ms

With range before sort, MongoDB had to collect every matching key from June onwards and sort them in memory to find the top 10. With sort before range, it walks the index in total order, checks the date on each entry, and stops after 10 matches. As data grows, the first gets steadily worse; the second stays flat. Large in-memory sorts can also hit memory limits and fail or spill to disk.

Equality fields first narrow the index to one section, the sort field next keeps entries in order, and range fields last filter while walking.
Equality fields first narrow the index to one section, the sort field next keeps entries in order, and range fields last filter while walking.

ESR is a guideline, not a law: if the range condition is extremely selective (it matches almost nothing), putting it before the sort can win. explain tells you which.

Index selectivity and partial indexes

Selectivity is how much a condition narrows the result. orderId is highly selective (one document); status with three values isn't (a third of the collection each). Among equality fields, order matters less than people think, because MongoDB can seek on all of them. What matters most is that the index puts the selective conditions where they can be used, follows ESR, and matches your sort.

Low-selectivity fields alone make poor indexes: an index on status alone still returns a third of the collection. Partial indexes can help when you only ever query a small subset, such as { status: "open" }:

JavaScript
db.orders.createIndex({ createdAt: 1 }, { partialFilterExpression: { status: "open" } })

How to design indexes for an application

  1. List the important queries with their filters and sorts.
  2. Design compound indexes using ESR, and let one index serve several queries through its prefixes.
  3. Check each query with explain("executionStats"): keys and docs examined close to nReturned, and no SORT stage where you expect index order.
  4. Remove indexes that are prefixes of others ({ customerId: 1 } is redundant next to { customerId: 1, status: 1 }).

Check yourself

The query on a and the query on a and b, because they use prefixes of the index. A query on b and c without a can't seek into the index, since entries are ordered by a first, so at best it scans the whole index.

The index then returns entries in sort order, so MongoDB can stop after enough matches instead of collecting every range match and sorting in memory.

Status has only a few distinct values, so each one still matches a large fraction of the collection, and MongoDB has to fetch most documents anyway. Combine it with more selective fields in a compound index, or use a partial index for a rare status.

Indexing only documents that match a filter, such as orders with status: "pending". The index stays small and fast while serving queries for that subset.

For single-field sorts no, but for sorts on several fields it does: { a: 1, b: -1 } supports sorting by a ascending and b descending, or the exact reverse, not both ascending.