Compound Indexes

A compound index covers multiple fields in a single, ordered index structure. Field order is not cosmetic — it determines which queries the index can serve efficiently, which is why the ESR rule (Equality, Sort, Range) exists as a design heuristic.

Creating a Compound Index

A three-field compound index

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

The ESR Rule: Equality, Sort, Range

When designing a compound index for a specific query, order the fields as: fields you filter with Equality, then fields you Sort by, then fields you filter with a Range. This ordering lets MongoDB narrow down as much as possible using the tree structure before falling back to scanning a range.

Query Need	Example Filter/Sort	ESR Category
Exact match	`{ status: "shipped" }`	Equality (E)
Ordering	`.sort({ createdAt: -1 })`	Sort (S)
Range condition	`{ total: { $gte: 50 } }`	Range (R)

Building an index for this exact query shape

// Query: shipped orders, newest first, total >= 50
db.orders.find({ status: "shipped", total: { $gte: 50 } }).sort({ createdAt: -1 })

// ESR-ordered index: Equality field first, Sort field second, Range field last
db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

Warning

Putting a range field before a sort field in a compound index often forces MongoDB to sort in memory anyway, even though an index exists — the range condition breaks the index's ability to deliver already-sorted output past that point. This is the most common compound-index design mistake.

The Prefix Rule

A compound index on { a: 1, b: 1, c: 1 } can serve queries on any left-to-right prefix of its fields — { a }, { a, b }, or { a, b, c } — but generally cannot efficiently serve a query that skips a leading field, like { b } or { a, c } alone.

Prefix usage

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

db.orders.find({ status: "shipped" })                                  // uses index (prefix: status)
db.orders.find({ status: "shipped", customerId: 101 })                 // uses index (prefix: status, customerId)
db.orders.find({ status: "shipped", customerId: 101, createdAt: {...} })// uses index (all 3 fields)

db.orders.find({ customerId: 101 })   // does NOT use this index — customerId is not a leading field
db.orders.find({ createdAt: {...} })  // does NOT use this index — createdAt is not a leading field

Tip

Because of the prefix rule, one well-designed 3-field compound index often makes up to three separate single-field indexes redundant — { status: 1 } alone is already covered by the leading prefix of { status: 1, customerId: 1, createdAt: -1 }. Dropping the now-redundant single-field index saves write overhead and storage with no read-side downside.

Sort Direction Pairs for Compound Sorts

For a compound sort across multiple fields, the index's per-field direction must match either exactly, or be exactly reversed on every field — you can't mix "matches forward on field A but reversed on field B" against a single index.

Matching and non-matching sort directions

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

db.orders.find().sort({ status: 1, createdAt: -1 })    // matches exactly — uses index
db.orders.find().sort({ status: -1, createdAt: 1 })    // exact reverse of the index — ALSO uses it

db.orders.find().sort({ status: 1, createdAt: 1 })     // neither match nor full reverse — in-memory SORT stage

Covered Queries with Compound Indexes

The more fields a compound index covers, the more likely a query's filter + projection can be answered entirely from the index — no document fetch required.

A covered query using a compound index

db.orders.createIndex({ status: 1, total: 1 })

db.orders.find({ status: "shipped" }, { total: 1, _id: 0 })
// filter field (status) AND projected field (total) are BOTH in the index → covered

Index Intersection vs a Purpose-Built Compound Index

MongoDB can combine two separate single-field indexes at query time (index intersection) to satisfy a multi-field filter, but it's generally less efficient than one well-designed compound index covering the same fields.

Approach	How It Works	Tradeoff
Index intersection	MongoDB scans two separate single-field indexes and intersects the matching document ids	More overhead merging two result sets; doesn't help with sort at all
Purpose-built compound index	One index, ordered by ESR, directly narrows to the matching + sorted range	Requires knowing your query patterns up front to design correctly

Note

Don't rely on index intersection as your primary optimization strategy — treat it as a fallback the planner might use, and design explicit compound indexes for your hot, known query patterns instead.

Real Query-Pattern-Driven Design Example

Say your application's dashboard consistently runs this exact query shape:

The query pattern to optimize for

// "Show this customer's shipped orders over $50, most recent first"
db.orders.find({
  customerId: 101,
  status: "shipped",
  total: { $gte: 50 }
}).sort({ createdAt: -1 })

ESR-ordered compound index for this exact pattern

db.orders.createIndex({
  customerId: 1,   // Equality
  status: 1,       // Equality
  createdAt: -1,   // Sort
  total: 1         // Range
})

{
  winningPlan: {
    stage: 'FETCH',
    inputStage: { stage: 'IXSCAN', indexName: 'customerId_1_status_1_createdAt_-1_total_1' }
    // NO separate SORT stage — the index already delivers createdAt-descending order
  },
  executionStats: { totalKeysExamined: 8, totalDocsExamined: 8, nReturned: 8 }
}

ESR rule: order compound index fields as Equality, then Sort, then Range.
A compound index serves any LEFT-TO-RIGHT prefix of its fields — plan field order around your most common query shapes.
A compound sort needs directions that either match the index exactly, or are the exact full reverse.
A well-designed compound index often makes several narrower single-field indexes redundant.
Prefer a purpose-built compound index over relying on index intersection for known, hot query patterns.