Compound Index Design

What we're doing: Apply ESR to a query where the fields appear in a different order than the index should use.

// Query as written, fields in this order: total (range), status (equality), createdAt (sort)
db.orders.find({ total: { $gte: 100 }, status: "shipped" }).sort({ createdAt: -1 })

// ESR classification: E = status, S = createdAt, R = total
// Index written in ESR order, NOT query-appearance order:
db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

1

The query happens to write total (a range field) before status (an equality field) — the order fields appear in the query has no bearing on index order.

5

ESR classification, not field-appearance order, decides the index: status (E) first, createdAt (S) second, total (R) last.

Why this works: ESR deliberately ignores the order a query happens to list its conditions in — the classification (what role each field plays) is what determines a good index, which is precisely why it produces the same answer regardless of how the query was typed.

What we're doing: Show an index designed from a query captured in the slow-query log, rather than guessed from the schema.

// Captured from the slow-query log — this is the query actually running in production:
db.orders.find({ customerId: 42, status: "pending" }).sort({ createdAt: -1 })

// Index designed to match it exactly, not a generic "index everything" index:
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })

2

This query came from the actual slow-query log, not a guess about what the app "probably" queries.

5

The index is built to exactly match this real query's equality fields and sort — nothing speculative added.

Why this works: A real captured query is verifiable evidence — you can check with explain() before and after that the index actually serves it — whereas an index designed from "this field seems important" has no such check until (or unless) the guessed query pattern actually occurs.

What we're doing: Show the same two fields, indexed in opposite orders, serving different queries as an efficient prefix.

db.orders.createIndex({ customerId: 1, status: 1 })
db.orders.find({ customerId: 42 })                    // efficient — customerId is the leading field
db.orders.find({ status: "pending" })                 // not efficient — status alone is not a prefix

// A second index, fields reversed:
db.orders.createIndex({ status: 1, customerId: 1 })
db.orders.find({ status: "pending" })                 // now efficient — status is the leading field here

1

With customerId leading, queries filtering by customerId (alone or with status) use this index well; status alone does not.

5

The reversed index is a genuinely different structure — it makes status-only queries efficient at the cost of an entirely separate index to maintain.

Why this works: This example is the concrete mechanism behind ESR and the prefix rule — neither is an arbitrary convention, both follow directly from the fact that a compound index's field order determines which field is the "outermost" sort key, and therefore which queries can use it as a prefix.

What we're doing: Show the specific efficiency cost of the "skip a middle field" case, since it works but is not free.

db.inventory.createIndex({ item: 1, location: 1, stock: 1 })

// Skips "location" — still uses the index (item is the leading field), but less efficiently:
db.inventory.find({ item: "yeast", stock: { $gt: 50 } })
// -> scans every index entry for "yeast" across all locations, then filters by stock in that range

1

The index is built for item → location → stock, in that nested order.

4

Skipping location means MongoDB still starts from the item prefix, but then has to scan every location under that item rather than jumping directly to a stock range — a dedicated { item, stock } index would be more efficient for this exact query.

Why this works: The prefix rule is not a strict "works or doesn't" binary — MongoDB is deliberately lenient about using a leading prefix even when a middle field is skipped, but that leniency has a real performance cost that only shows up in explain()'s totalDocsExamined, not in whether the query "uses an index" at all.

What we're doing: Contrast the write cost of one compound index versus several single-field indexes covering the same fields.

// Five separate single-field indexes: an insert touching all five fields updates all five
db.orders.createIndex({ customerId: 1 })
db.orders.createIndex({ status: 1 })
db.orders.createIndex({ createdAt: 1 })
db.orders.createIndex({ total: 1 })
db.orders.createIndex({ region: 1 })
// -> one insertOne() call now does 6 index updates (5 + the default _id index)

2

Five indexes look individually harmless, but a single insert that populates all five fields pays for all five index updates.

6

The write cost is additive across every index that covers a field the write touches — this is the concrete mechanism behind "indexes are not free."

Why this works: The read/write trade-off is easy to state abstractly but easy to underweight in practice, because the read benefit (a fast query) is immediately visible while the write cost (slightly slower inserts, more memory pressure) accumulates quietly across every write, everywhere.

What we're doing: Show write throughput and memory pressure both degrading as index count grows on the same collection, without any single index looking obviously wrong.

// Over a year, feature after feature adds "just one more index" to the same collection:
// 2 indexes at launch -> 4 after feature A -> 7 after feature B -> 11 after feature C

// None added recklessly, but the collection now maintains 11 index updates per write,
// and its combined index size no longer fits comfortably in the deployment's available RAM
db.orders.insertOne({...})  // one write, eleven index updates

2

Each individual index addition seemed reasonable at the time it was added, for the feature that needed it.

5

The cumulative effect — eleven index updates per write, and a combined index size competing with hot data for RAM — is what actually causes the slowdown, not any single index in isolation.

Why this works: This is a gradual, cumulative effect rather than a single obvious mistake — which is exactly why it needs periodic review (checking real index usage, section 59) rather than being caught by inspecting any one index-creation decision on its own.

What we're doing: Show a case where a compound index alone is insufficient, because one of its fields is also queried on its own frequently.

// Common: filtering by customer AND status together
db.orders.createIndex({ customerId: 1, status: 1 })

// Also common, independently: support agents looking up a single order by tracking number alone
db.orders.createIndex({ trackingNumber: 1 })
// -> two indexes, each serving a genuinely different, real query pattern — neither replaces the other

2

This compound index efficiently serves "this customer's orders, optionally filtered by status" — the common storefront query.

6

trackingNumber is not a prefix of the compound index, so a separate index is the only way to make that independent lookup efficient — the compound index would not help it even though trackingNumber technically exists as a field on the same documents.

Why this works: A collection commonly has more than one real, independent query shape — recognizing that a single compound index cannot efficiently serve every shape is what leads to the right mix of one compound index plus targeted separate indexes, rather than forcing everything into one.