Compound Indexes

A compound index is a single index built across two or more fields in a document, stored as one sorted structure rather than several separate ones. Almost every real application needs compound indexes because real queries filter and sort on more than one field at a time — a single-field index can only help with one of those fields efficiently. Understanding how MongoDB builds and uses compound indexes is one of the highest-leverage skills for writing fast queries.

Overview / How it works

Internally, every MongoDB index — single-field or compound — is a B-tree. For a compound index on { a: 1, b: 1, c: 1 }, MongoDB does not build three separate trees. It builds one tree whose keys are concatenations of the three field values, sorted first by a, then within each a value by b, then within each b value by c. This is exactly like a phone book sorted by last name, then first name, then middle name: you can jump straight to “Smith, John” efficiently, but you cannot efficiently find everyone named “John” without scanning the whole book, because the book isn’t organized by first name first.

That sort order is the single most important thing to understand about compound indexes: field order determines what the index can do for you. An index on { status: 1, orderDate: -1 } can satisfy queries that filter on status alone, or on status and orderDate together, or that sort by orderDate once status is fixed by an equality match. It generally cannot efficiently satisfy a query that filters on orderDate alone, because the index isn’t sorted by orderDate at the top level.

The prefix rule

A compound index on fields (a, b, c) can support any query that uses a prefix of those fields, in order: (a), (a, b), or (a, b, c). It cannot efficiently support (b), (c), or (b, c) alone, because those don’t start from the beginning of the sorted structure. This is why one well-designed compound index often replaces several single-field indexes: an index on { customerId: 1, status: 1 } covers queries filtering by customerId alone AND queries filtering by both customerId and status — you don’t need a separate index on just customerId.

The ESR rule

When you design a compound index for a query that has equality matches, a sort, and range conditions, order the fields as Equality, Sort, Range — ESR for short. Equality fields (status: "shipped") go first because they narrow the index to a single contiguous slice. Sort fields go next so MongoDB can walk that slice in already-sorted order instead of an in-memory sort. Range fields ($gt, $lt, $in with many values) go last, because once you introduce a range, the index can no longer stay perfectly sorted for anything after it — range conditions effectively “break” the usefulness of subsequent fields in the index.

Syntax

db.collection.createIndex(
  { field1: 1, field2: -1, field3: 1 },
  { name: "idx_name", unique: false, partialFilterExpression: {} }
);

Index specification document (first argument) — the fields to index, in the order they should be sorted, each mapped to 1 (ascending) or -1 (descending). Order here is not cosmetic; it defines what the index can be used for.
name — optional custom index name; otherwise MongoDB generates one like customerId_1_status_1.
unique — if true, rejects documents whose combination of indexed field values duplicates an existing one.
partialFilterExpression — indexes only documents matching a filter, keeping the index smaller.
background — removed in modern MongoDB; index builds are non-blocking by default since 4.2 and this option is ignored.

A compound index can have at most 32 fields, and it cannot include more than one field indexed as a multikey (array) field — MongoDB will reject an index on two array fields at once.

Examples

Example 1: creating and using a compound index

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

Output:

customerId_1_status_1

The return value is simply the generated name of the new index. Now a query filtering on both fields can use it:

db.orders.find({ customerId: "C1001", status: "shipped" });

Output:

[
  {
    _id: ObjectId("66b1f2a1c9e77a001f3d9b21"),
    customerId: "C1001",
    status: "shipped",
    orderDate: ISODate("2026-07-20T00:00:00.000Z"),
    amount: 89.99
  }
]

MongoDB uses the compound index to jump directly to the customerId: "C1001" slice, then within it to the status: "shipped" entries, instead of scanning every order in the collection.

Example 2: verifying index usage with explain()

db.orders.find({ customerId: "C1001", status: "shipped" }).explain("executionStats");

Output (trimmed):

{
  queryPlanner: {
    winningPlan: {
      stage: "FETCH",
      inputStage: {
        stage: "IXSCAN",
        indexName: "customerId_1_status_1",
        direction: "forward"
      }
    }
  },
  executionStats: {
    totalDocsExamined: 1,
    totalKeysExamined: 1
  }
}

The key thing to check is stage: "IXSCAN" and indexName. If instead you see stage: "COLLSCAN", MongoDB is reading every document in the collection because no usable index exists — that’s the signal to design one. totalKeysExamined close to totalDocsExamined (and both small) means the index is doing its job precisely, not just being used loosely.

Example 3: applying the ESR rule

Suppose you frequently run this query: find shipped orders, sorted by the newest first, within a date range.

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

db.orders
  .find({
    status: "shipped",
    orderDate: { $gte: ISODate("2026-07-01"), $lte: ISODate("2026-07-31") }
  })
  .sort({ orderDate: -1 });

Output:

[
  {
    _id: ObjectId("66b1f2a1c9e77a001f3d9b40"),
    customerId: "C1042",
    status: "shipped",
    orderDate: ISODate("2026-07-29T00:00:00.000Z"),
    amount: 154.5
  }
  // ... more shipped orders, newest first
]

Here status is the equality field so it comes first. orderDate is both the sort field and a range filter; because it’s a single field, ESR still puts it second (equality always comes before it) and MongoDB can use the index for the sort directly without an extra in-memory sort step, avoiding the dreaded SORT_KEY_GENERATOR/blocking sort stage you’d see in explain() otherwise.

How it works step by step

The query planner looks at the query’s filter and sort, and compares them against the indexes available on the collection.
It identifies candidate indexes whose field order forms a usable prefix for the query — equality fields matched first, matching the index’s leading fields.
If several indexes could work, the planner runs a short trial (“multi-plan”) of the top candidates and picks the one that returns results fastest, caching that choice for similar future queries.
During execution, an IXSCAN stage walks the B-tree, following the sorted structure to jump directly to the matching range of keys instead of touching every document.
Each matched index entry stores the indexed field values plus the document’s _id; a FETCH stage then retrieves the full document from the collection using that _id, unless the query is covered (every requested field is present in the index itself, letting MongoDB skip the fetch entirely).

Common Mistakes

Mistake 1: wrong field order for the query pattern

// Query filters by status, but index leads with orderDate
db.orders.createIndex({ orderDate: -1, status: 1 });
db.orders.find({ status: "shipped" });

This index cannot be used efficiently for a query that filters only on status, because status isn’t the leading field — MongoDB would fall back to a collection scan. Put the field that appears as an equality filter in your most common query first:

db.orders.createIndex({ status: 1, orderDate: -1 });
db.orders.find({ status: "shipped" }); // now uses the index prefix

Mistake 2: creating redundant single-field indexes alongside a compound one

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

The first index is now redundant: any query that could use { customerId: 1 } alone can also use the leading prefix of { customerId: 1, status: 1 }. The extra index just costs write overhead and disk space with no query benefit. Drop it:

db.orders.dropIndex({ customerId: 1 });

Mistake 3: putting a range field before the sort field (breaking ESR)

// amount is a range filter placed before the orderDate sort
db.orders.createIndex({ status: 1, amount: 1, orderDate: -1 });
db.orders
  .find({ status: "shipped", amount: { $gt: 50 } })
  .sort({ orderDate: -1 });

Because amount is a range condition sitting before orderDate, the index can’t stay sorted by orderDate across the matched amount range, forcing an in-memory sort. Reordering to follow ESR (Equality, Sort, Range) fixes it:

db.orders.createIndex({ status: 1, orderDate: -1, amount: 1 });

Best Practices

Design compound indexes around your application’s actual query shapes, not speculatively — use db.collection.aggregate([{ $indexStats: {} }]) or the free Atlas Performance Advisor to see which indexes are actually used.
Follow the ESR rule (Equality, Sort, Range) when a query combines all three kinds of conditions.
Let the prefix rule do double duty: one well-ordered compound index can replace several narrower single-field indexes.
Always confirm index usage with .explain("executionStats") rather than assuming — look for IXSCAN, not COLLSCAN.
Keep the total number of indexes on a collection reasonable; every index adds overhead to every insert, update, and delete because MongoDB must keep all of them in sync.
Use partialFilterExpression to index only the subset of documents you actually query (e.g. only status: "active" orders) when the collection is large and most documents fall outside your hot query pattern.
Remember a compound index with only ascending fields can technically be scanned in reverse too, but mixed ascending/descending field directions matter for sorts that combine both directions in the same query.

Practice Exercises

You have a db.reviews collection where most queries filter by productId and sort by rating descending. Design and create the compound index that best supports this, and verify with explain() that it produces an IXSCAN.
Given an index on { category: 1, price: 1 }, decide (without running it) whether a query db.products.find({ price: { $lt: 20 } }) can use this index efficiently, and explain why using the prefix rule.
Rewrite the field order of a proposed index { createdAt: -1, userId: 1 } for a query that filters by userId (equality) and sorts by createdAt, applying the ESR rule, then create it and confirm with .explain() that no in-memory sort stage appears.

Summary

A compound index is one sorted B-tree structure spanning multiple fields, sorted by the first field, then the second within each value of the first, and so on.
Field order determines usefulness: a compound index on (a, b, c) supports queries on the prefixes (a), (a, b), and (a, b, c), but generally not (b) or (c) alone.
Follow the ESR rule — Equality fields first, then Sort fields, then Range fields — when designing an index for a query with mixed condition types.
Always verify index usage with .explain("executionStats"), checking for IXSCAN versus COLLSCAN.
One well-designed compound index can replace multiple redundant single-field indexes thanks to the prefix rule.
Every index has a write-side cost, so design intentionally around real query patterns rather than indexing every field.

Compound Indexes

Overview / How it works

The prefix rule

The ESR rule

Syntax

Examples

Example 1: creating and using a compound index

Example 2: verifying index usage with explain()

Example 3: applying the ESR rule

How it works step by step

Common Mistakes

Mistake 1: wrong field order for the query pattern

Mistake 2: creating redundant single-field indexes alongside a compound one

Mistake 3: putting a range field before the sort field (breaking ESR)

Best Practices

Practice Exercises

Summary

Table of Contents