MongoDB Compound Index Order: Treat Key Sequence as an Interface, Not a Tuning Knob

A collection can have an index on every field a query touches and still return slowly. The usual cause is not a missing index but a mis-ordered one. A compound index is an ordered path through its keys, and MongoDB can only walk that path from the front — so the sequence you declare decides which queries get an indexed read and which fall back to a collection scan.

The practical takeaway: derive key order from the query shapes you must support, then confirm the plan with explain("executionStats") instead of assuming an index is being used.

Why key order is a contract

An index {customerId: 1, status: 1, createdAt: -1} can serve queries that filter on customerId, on customerId plus status, or on all three keys. It cannot efficiently serve a query that filters on status alone, because status is not a prefix of the index. MongoDB will not raise an error in that case — it simply chooses a different plan, often COLLSCAN, and the query gets slower as the collection grows.

This is the failure mode that makes index problems hard to spot in review: the code looks indexed, the schema looks indexed, and the plan is still a full scan.

The ESR ordering rule

MongoDB's documented guidance for ordering compound index keys is Equality, Sort, Range:

1. Equality fields first. Predicates like {status: "shipped"} pin the scan to a contiguous key range.
2. Sort fields next. If the index supplies the requested order, the planner can drop the blocking SORT stage that otherwise buffers the whole result set in memory.
3. Range fields last. A range predicate such as {createdAt: {$gte: ...}} does not narrow to a single key, so any key after it cannot be used to preserve sort order.

ESR is guidance, not a law. Among several equality fields, put the more selective one first when you can — a low-cardinality field like status may only split the index into a handful of large ranges. And if a sort cannot be satisfied by the index at all, no ordering of keys will remove the blocking sort; you need a different index or a different query.

Worked example: recent orders for one customer

Assume an orders collection with documents shaped roughly like {customerId, status, createdAt, total}, and a query that fetches the newest shipped orders for a single customer. Run this in mongosh against a non-production copy first; createIndex takes a write lock on the collection while it builds (background builds are the default in modern versions, but the build still consumes I/O).

db.orders.createIndex(
  { customerId: 1, status: 1, createdAt: -1 },
  { name: "customer_status_recent" }
)

Both customerId and status are equality predicates, so they come first; createdAt is the sort key, so it comes last. Now inspect the plan:

db.orders.find({ customerId: "c-1042", status: "shipped" })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain("executionStats")

The example is illustrative — run it against your own data and read the fields rather than trusting a copied number. What matters in the output:

Field	What you want to see
winningPlan.stage	LIMIT over FETCH over IXSCAN. A COLLSCAN or a SORT stage means the index is not doing its job.
executionStats.totalKeysExamined	Close to nReturned. A ratio in the thousands means the index is being scanned broadly rather than narrowed.
executionStats.totalDocsExamined	Close to nReturned. Large values mean many documents are being fetched only to be discarded.
executionStats.executionTimeMillis	Useful for before/after comparison, not as an absolute target — it varies with cache state and load.

If the plan looks wrong, hint() a candidate index and re-run explain to compare. Creating an index clears the plan cache, so always re-check plans after an index change rather than assuming the old plan still applies.

Covered queries: the zero-fetch case

If every field in the projection is present in the index, MongoDB can answer without reading the document at all. The catch is that _id is included by default, so you must exclude it explicitly:

db.orders.find(
  { customerId: "c-1042", status: "shipped" },
  { _id: 0, createdAt: 1 }
).explain("executionStats")

A covered plan shows totalDocsExamined: 0 and a projection stage instead of a FETCH. Add total to that projection and coverage is lost, because total is not in the index. Multikey indexes — those over array fields — cannot cover queries at all.

Trade-offs and limits

• Write cost. Every index must be updated on insert, update, and delete. A three-key compound index is not free, and the cost scales with write volume.
• Memory. Indexes compete for RAM with the working set. An index that is rarely used but large can push hot data out of cache.
• Silent prefix mismatch. Nothing fails loudly when a query stops matching an index prefix; only the plan changes.
• Index intersection is not a substitute. Do not design around MongoDB combining two single-field indexes to emulate a compound one.

To find indexes that are not earning their keep, run db.orders.aggregate([{ $indexStats: {} }]) and read accesses.ops per index. One caveat: these counters reset when mongod restarts, so a zero count immediately after a restart is not evidence of disuse. Collect over a representative window — ideally a full business cycle — before dropping anything.

What to do next

List the handful of query shapes that matter most, in their real filter-sort-projection form. Derive one compound index per shape using ESR, verify each with explain("executionStats"), and check the keys-examined-to-returned ratio rather than the wall-clock time alone. Re-run the same checks after schema changes and deploys, because query shapes drift and an index that was correct last quarter may no longer match anything.