Compound and Multikey Indexes: The ESR Rule

A single-field index answers a single-field question. Real queries filter on several fields and sort on another, and the order in which you list those fields in a compound index decides whether the query is served from the index in one pass or degrades into a scan plus an in-memory sort. After this lesson you will be able to design compound indexes with the Equality-Sort-Range rule, know which queries an index can serve through its prefixes, and understand how MongoDB indexes array fields.

How a Compound Index Is Ordered

{ customerId: 1, status: 1, createdAt: -1 } stores one entry per document, sorted first by customerId, then by status within each customer, then by createdAt (descending) within each status. Because entries are physically ordered this way, the index can only be entered from the left:

| Query fields | Uses the index? | |---|---| | { customerId } | yes, prefix | | { customerId, status } | yes, prefix | | { customerId, status, createdAt } | yes, full key | | { customerId, createdAt } | partially: seeks on customerId, filters createdAt inside the scanned range | | { status } or { createdAt } | no, not a prefix |

This is the prefix rule. It also means { customerId: 1 } is redundant once { customerId: 1, status: 1 } exists.

The ESR Rule: Equality, Sort, Range

When a query combines exact matches, a sort and a range, order the index fields as:

Equality fields first (status: "open", customerId: 42). Each equality narrows the scan to one contiguous block of keys.
Sort fields next. Within that block the keys are already in sort order, so the server streams results without a SORT stage and without the 100 MB in-memory limit.
Range fields last (total: { $gt: 100 }, $in with many values, $ne). A range splits the block, so anything after it can no longer be used for ordering.

javascript

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

const stats = db.orders
  .find({ customerId: 42, status: "open", total: { $gt: 100 } })
  .sort({ createdAt: -1 })
  .explain("executionStats").executionStats;

stats.totalKeysExamined;   // close to nReturned
stats.totalDocsExamined;   // equals nReturned: only matching documents were fetched

Put the range field before the sort field and the plan changes to an IXSCAN followed by a blocking SORT. Both plans use the index, but only the ESR order lets the index do the sorting.

Sort Direction Matters

A single-field index serves both sort directions because the server can walk it backwards. For compound sorts the pattern must match the index or its exact inverse:

javascript

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

db.orders.find().sort({ status: 1, createdAt: -1 })   // index order
db.orders.find().sort({ status: -1, createdAt: 1 })   // inverse: still fine
db.orders.find().sort({ status: 1, createdAt: 1 })    // mixed: in-memory SORT

Multikey Indexes: Indexing Arrays

Index a field that holds arrays and MongoDB automatically creates a multikey index with one key per element, so { tags: "office" } is an index lookup rather than a scan:

javascript

db.products.createIndex({ tags: 1 })
db.products.find({ tags: "office" }).explain().queryPlanner.winningPlan
// ... "isMultiKey": true ...

Rules that follow from one-key-per-element:

A compound index may contain at most one array field; otherwise the number of keys would be the product of the array lengths.
Large arrays mean many index entries per document, which slows writes and inflates index size.

Choosing and Auditing Indexes

Design one index per distinct query shape, then let the prefix rule collapse overlaps. Verify what exists and what is actually used:

javascript

db.orders.getIndexes()
db.orders.aggregate([{ $indexStats: {} }])   // per-index usage counters since restart
db.orders.dropIndex("customerId_1")           // remove a redundant prefix index

Every index costs write throughput and RAM; the working set of indexes should fit in memory. An index that $indexStats shows with accesses.ops: 0 for weeks is a candidate for removal.

Common Mistakes

Leading with the range field because it "looks selective". Equality first, then sort, then range.
Creating one index per field and expecting the planner to combine them. Index intersection is rare; compound indexes are the norm.
Keeping single-field indexes that are prefixes of compound ones.
Indexing two array fields in one compound index. The server rejects it with a "cannot index parallel arrays" error.

Key Takeaways

Compound index entries are sorted by the first field, then the second, and so on; queries can use any left-hand prefix.
Order fields as Equality, Sort, Range so the index both narrows the scan and returns results pre-sorted.
Compound sorts must match the index pattern or its exact inverse; single-field indexes work in both directions.
Indexing an array field creates a multikey index with one key per element; only one array field per compound index.
Audit with getIndexes() and $indexStats, and drop indexes that are prefixes of others.

Next lesson: Unique, Partial, TTL and Wildcard Indexes — enforce uniqueness, index only the documents that matter, expire data automatically and index unpredictable fields.