Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 70 ยท Indexes and performance

Designing Compound Indexes

Learn how to order fields in a MongoDB compound index with the ESR rule, the prefix rule, sort direction and covered queries, with runnable demos.

A compound index is only as good as the order of its fields. The same three fields in a different order can give you a query that finishes instantly, one that sorts a hundred thousand documents in memory, or an index MongoDB ignores completely. Because the order decides everything, it is worth learning a few rules instead of guessing.

This lesson teaches the practical rules: the ESR rule (Equality, Sort, Range), the prefix rule, how sort direction interacts with the index, and what makes a query covered. You will also bust the myth that several single-field indexes can stand in for one good compound index. The demos run on a JavaScript stand-in that models an index as a sorted list.

The createIndex, find and explain commands are real mongosh; the TryIts are a hand-written model of how a sorted index is walked. Exact key counts on a real server differ because MongoDB's planner and B-tree do more than the model, but the shape of the trade-off is the same.

The ESR rule

Equality Fields tested with exact matches go first.
Sort Fields you sort by come next.
Range Fields with $gt, $lt or $in ranges go last.

Equality, then Sort, then Range.

1 / 2

The prefix rule

An index on { a: 1, b: 1, c: 1 } is sorted by a, then b within each a, then c. It therefore supports queries on:

  • a
  • a and b
  • a, b and c

It does not support a query on b alone or on c alone, or on b and c together, because those values are scattered across the whole index. One compound index replaces the single-field index on its first field, so { a: 1, b: 1 } makes a separate { a: 1 } redundant. Dropping redundant prefixes saves write time and memory.

The ESR rule

Consider a typical query on an orders collection: shipped orders with an amount above a threshold, newest first, five at a time.

JavaScript
db.orders.find({ status: "shipped", amount: { $gt: 100 } })
         .sort({ createdAt: -1 })
         .limit(5)

This query has an equality field (status), a sort field (createdAt) and a range field (amount). The ESR rule says to order the index in that same sequence:

JavaScript
db.orders.createIndex({ status: 1, createdAt: 1, amount: 1 })

Why this order? After the equality on status, the entries are already sorted by createdAt, so MongoDB reads them in the order you asked for and can stop after five matches. If the range field came before the sort field, the matching entries would be ordered by amount instead, and MongoDB would have to collect all of them and sort them in memory before returning five. A blocking in-memory sort uses a lot of memory on big result sets, and it has a limit of 100 MB by default; depending on version and settings it either spills to disk or fails.

Equality fields can be in any order among themselves for ESR purposes. Put first the ones that your other queries also use alone, so the index serves more queries as a prefix.

The stand-in below builds both indexes over 600 generated orders and runs the query two ways.

JS Sort before range, or range before sort?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

With a loose range (amount > 100) the ESR index examines just 5 entries and never sorts, while the range-first index examines all 230 shipped orders above 100 and sorts them. With a very selective range (amount > 480), the ESR walk has to examine 149 entries before it finds five matches, while the range-first index touches only 7 entries but still needs the in-memory sort. That is why ESR is a guideline, not a law: it is the best default, and explain() (explain() and the Profiler) tells you what really happens on your data.

In this model the ESR walk checks the amount value of every entry it passes. A real MongoDB server can apply the range as index bounds while it scans, which skips non-matching entries more cheaply, so real counts will usually be lower.

Sort direction

For a single-field index, direction never matters. For a compound index, a sort can use the index only if it matches the index order or its exact reverse.

With { a: 1, b: -1 }:

  • sort({ a: 1, b: -1 }) works (same direction).
  • sort({ a: -1, b: 1 }) works (every field reversed, so MongoDB walks the index backwards).
  • sort({ a: 1, b: 1 }) does not work, because the directions are mixed relative to the index.

If the query also has an equality on a field, that field is fixed and drops out of the sort comparison. With the index { status: 1, createdAt: 1 } and the filter { status: "shipped" }, a sort on { createdAt: -1 } is supported by walking the status = "shipped" section backwards.

Covered queries

A query is covered when MongoDB can answer it entirely from the index without reading any document. It requires three things:

  1. Every field in the filter is in the index.
  2. Every field in the result is in the index, so you must use an inclusion projection (see Projection).
  3. _id is either part of the index or excluded with _id: 0.
JavaScript
db.orders.createIndex({ status: 1, amount: 1 })
db.orders.find(
  { status: "shipped", amount: { $gt: 100 } },
  { _id: 0, status: 1, amount: 1 }
)

explain("executionStats") then reports totalDocsExamined: 0, and the plan has no FETCH stage. Covered queries are very fast because they avoid random document reads. They do not work on multikey fields (array indexes), because the index does not hold the whole array.

The next stand-in codes the three rules as small checker functions. It is a simplified model of the planner, but it applies the same logic, so you can try your own index and query shapes.

JS A tiny rule checker for index design

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The case equality on a, sort b:1 is the interesting one. With a fixed to a single value, the entries are already in b order. The index stores b descending, and b: 1 is its exact reverse, so MongoDB walks that section backwards and no sort is needed.

The index intersection myth

It is tempting to think that single-field indexes on status, createdAt and amount are as good as one compound index, because MongoDB "can combine indexes". MongoDB does have an index-intersection strategy, but the planner rarely chooses it. Combining two index scans needs extra work, and it cannot provide a sort order the way a compound index does. In practice a query that filters and sorts on several fields wants one compound index shaped for that query. Do not count on intersection to save a missing compound index.

A short design routine

  1. List your important queries (filter fields, sort, projection, limit).
  2. For each, write the fields as E, S, R, and build the index in that order.
  3. Check whether one index can serve several queries through its prefixes, and drop indexes that are only prefixes of others.
  4. Run explain("executionStats") and compare totalKeysExamined and totalDocsExamined with nReturned.
  5. Re-check after the data grows, because a plan that was fine at 10,000 documents may not be at 10 million.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A query filters on status with equality, sorts by createdAt and filters amount with $gt. Which index follows the ESR rule?

    ESR means Equality (status), then Sort (createdAt), then Range (amount). That lets MongoDB read matching entries in sort order and stop early.
  2. 2. An index is { a: 1, b: 1, c: 1 }. Which query cannot use the index efficiently?

    Only a leftmost prefix (a, then a and b, then all three) is usable. A filter on b and c skips the first field, so the entries are not grouped for it.
  3. 3. With the index { a: 1, b: -1 }, which sort can the index NOT deliver without an in-memory sort?

    A sort must match the index order or its exact reverse. { a: 1, b: 1 } has a different direction relationship between the fields, so it matches neither.
  4. 4. What is required for a query to be covered by an index?

    A covered query is answered from index entries alone, so every field it needs must be in the index, and _id must not force a document fetch.

Summary

  • Only a prefix of a compound index's fields can be used on its own, so field order decides which queries benefit.
  • The ESR rule: put Equality fields first, then Sort fields, then Range fields, so results arrive in sort order and the scan can stop early.
  • ESR is a default, not a law: a very selective range can favour range-first, so verify with explain().
  • A sort uses a compound index when it matches the index order or its exact reverse; equality-fixed fields drop out of the comparison.
  • A covered query needs all filter and result fields in the index (with _id excluded or indexed) and shows no FETCH stage.
  • Do not rely on index intersection: build one compound index shaped for the query.

Next, learn to see what the server is doing in explain() and the Profiler.