beginnerintermediateadvanced

MongoDB · Compound Index Design

Compound Index Design

The ESR procedure, the prefix rule, and weighing compound vs. separate indexes against real cost

ESR: equality, sort, rangea repeatable field-ordering procedure
Prefix ruleonly leading field subsets are efficient
Order ≠ relabeling{ a, b } and { b, a } are different indexes
More indexes, more write costand they compete with data for RAM

1.ESR: equality, sort, range

ESR is a naming convention for the ordering rule section 10 introduced: put Equality fields first, Sort fields second, Range fields last, when designing a compound index for a specific query. It is a repeatable procedure, not a one-off insight.

Key features

E — equality: exact-match fields, e.g. { status: "shipped" }
S — sort: fields the query calls .sort() on
R — range: $gt/$gte/$lt/$lte/$in-with-many-values fields
Write the compound index in E, S, R order, regardless of the order fields appear in the query
A field never used as equality, sort, or range in the target query does not belong in this index

Classifying a query's fields for ESR

Classifying a query's fields for ESR
Query clause	ESR role	Index position
{ status: "shipped" }	Equality (E)	1st
.sort({ createdAt: -1 })	Sort (S)	2nd
{ total: { $gte: 100 } }	Range (R)	3rd

A repeatable procedure, not a one-off judgment call

read the query

find + sort

label each field

E, S, or R

index in E-S-R order

regardless of query field order

Command

text

// For a target query: db.createIndex({ <equality fields>: 1, <sort fields>: dir, <range fields>: 1 })

ESR: classify each field as Equality, Sort, or Range for the target query, then build the compound index in that order — regardless of how the query happens to be written.

2.Designing indexes from real query patterns

Start from the application's actual, measured queries — not from the fields that look "important," and not from every field a document happens to have. A field with no query filtering, sorting, or ranging on it has no reason to be in any index.

Key features

Start from logged or known query shapes (filters, sorts) the app actually issues, not the schema alone
A field that is never filtered, sorted, or ranged on has no index-design reason to exist in any index
The MongoDB profiler and slow-query log (section 15) are the concrete source of "real query patterns"
Different query shapes on the same collection often need different, separately-designed indexes
Index design driven by real queries is verifiable — explain() confirms whether the resulting index actually helps

Command

text

// Design order: capture the real query → classify its fields (ESR) → build the index → verify with explain()

Design indexes from real, captured query shapes — filters, sorts — not from the schema's field list or a guess at what "seems important." Verify the result with explain().

3.Index field order changes what the index can do

createIndex({ a: 1, b: 1 }) and createIndex({ b: 1, a: 1 }) are two different indexes, not the same index described two ways. They serve overlapping but different sets of queries efficiently — this is the concrete fact the prefix rule and ESR both build on.

Key features

createIndex({ a: 1, b: 1 }) ≠ createIndex({ b: 1, a: 1 }) — two distinct index structures
Each order efficiently serves a different set of query shapes as a usable prefix
MongoDB does not automatically create or substitute the reordered version for you
Choosing the wrong order for the dominant query means paying write cost for a structure the main query cannot fully use
This is why ESR gives an explicit procedure — order is a real design decision, not a detail to skip

Command

text

// createIndex({ a: 1, b: 1 }) and createIndex({ b: 1, a: 1 }) are two separate indexes to build and maintain

Field order in a compound index defines a genuinely different structure, not a relabeling — { a, b } and { b, a } serve different queries efficiently. Order is a real design decision, not a detail.

4.Compound index prefix behavior

A prefix of createIndex({ a: 1, b: 1, c: 1 }) is any leading subset in order: { a }, { a, b }, or the full { a, b, c }. MongoDB can also use the index for { a, c } (skipping b), but less efficiently. It cannot efficiently use it for { b }, { c }, or { b, c } alone — none of those are a prefix.

Key features

Prefixes of { a, b, c } are: { a }, { a, b }, and { a, b, c } — leading subsets, in order
A query on the full prefix, or any leading prefix, uses the index efficiently
A query on a non-leading field alone ({ b } or { c }) generally cannot use the index
MongoDB can use the index for a non-prefix combination like { a, c } (skipping b), but less efficiently than a purpose-built index
The "skip a middle field" case still requires the leading field(s) to be present in the query

Which queries use { item: 1, location: 1, stock: 1 } efficiently

Which queries use { item: 1, location: 1, stock: 1 } efficiently
Query filters on	Uses this index?
item	yes — a prefix
item, location	yes — a prefix
item, location, stock	yes — the full index
item, stock (location skipped)	can, but less efficiently than a dedicated { item, stock } index
location	no — not a prefix
stock	no — not a prefix
location, stock	no — item, the leading field, is missing

Prefixes of { item, location, stock }

{ item }

a prefix

{ item, location }

a prefix

{ item, location, stock }

the full index — also a prefix

Command

text

// For createIndex({ a: 1, b: 1, c: 1 }), the efficient prefixes are: { a }, { a, b }, { a, b, c }

A prefix of { a, b, c } is { a }, { a, b }, or { a, b, c } — leading subsets, in order. Non-prefix queries (a field alone that isn't leading) generally cannot use the index; skipping a middle field still works, but less efficiently.

5.Indexes trade write and storage cost for read speed

Every index is extra storage, and every write touching an indexed field also updates every index covering that field. A collection with ten indexes pays that cost ten times on a write that touches all ten fields — the read speedup is not free.

Key features

Storage: each index is its own on-disk (and often in-memory) structure, sized roughly by indexed field count and cardinality
Write cost: an insert/update touching an indexed field updates that index too — more indexes, more updates per write
The cost applies per index, not per field — a compound index is one update, but N separate indexes on N fields is N updates
A read-heavy, rarely-written collection can afford more indexes than a write-heavy one
The trade-off is why index design is a decision, not a "more is always better" default

Command

text

// Every index on a collection adds to the cost of every write that touches its field(s) — weigh against actual read benefit

Every index adds storage and a write-time update cost, paid on every write touching its field(s) — more indexes means more write cost, so index count is a trade-off against the collection's real write volume, not a free win.

6.How too many indexes hurt writes and memory

Beyond the per-write update cost, indexes compete for the same memory (the working set) as the data itself — MongoDB performs best when its hot indexes and hot data both fit in RAM. Too many indexes, or indexes rarely used, push that memory budget past what fits, forcing more disk activity even for reads.

Key features

Indexes occupy memory the same way frequently-accessed data does — they compete for the same working set
Too many indexes (or unused ones) can push the working set past available RAM, forcing disk access
Write throughput drops as more indexes require updating per write, independent of the memory effect
The symptom often looks like "the database got slower for no reason" — the real cause is index count creeping up over time
Unused indexes provide none of the read benefit while still paying the full storage, memory, and write cost

Command

text

// Periodically review index usage (section 59) — an index earns its write/memory cost only if something actually queries through it

Indexes compete with hot data for the same RAM working set — too many (or unused) indexes force disk access even for reads, on top of the per-write update cost. Review and prune index usage periodically, not just add.

7.Separate indexes vs. one compound index

A compound index wins when queries commonly filter on the same combination of fields together. Separate single-field indexes win when the fields are queried independently, in different combinations, or when writes only ever touch one of the fields at a time.

Key features

Compound index wins: queries commonly filter on the same field combination together
Separate indexes win: fields are queried independently, in varying combinations, or updated independently
A compound index on { a, b } does not replace a separate index on { b } alone if b-only queries are common
Writes that touch only one of several fields in a compound index still update the whole compound index
This decision is exactly what applying ESR/real-query-pattern design (this section's earlier concepts) resolves per query shape

Signal → which to choose

Signal → which to choose
Signal	Choose
Queries always filter on the same 2–3 fields together	one compound index over those fields
Fields are queried independently, in different combinations	separate single-field indexes
One field is updated far more often than the others	consider a separate index for it, not folded into a shared compound index
A field is queried both alone and combined with another	a compound index with that field leading often covers both, per the prefix rule

Which fits this query shape?

One compound index

Fields queried together, same combination
{ customerId: 1, status: 1 }
Serves the storefront query efficiently

Separate indexes

Fields queried independently
{ trackingNumber: 1 } on its own
Not a prefix of the compound index

Command

text

// Ask: are these fields usually queried together, or independently? The answer decides compound vs. separate.

Compound index: fields commonly queried together. Separate index: a field queried independently, in varying combinations, or updated on its own. A collection often needs both, for different real query shapes.

Spotted a mistake on this page? Tell us.

Read Sheet