5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule

Compound Indexes & the Equality-Sort-Range (ESR) Rule

Exam Focus: The MongoDB Certified Associate Developer Exam tests compound index creation syntax (up to 32 fields), multi-field sort direction compatibility (inversion rule), index prefix evaluation, step-by-step application of the Equality-Sort-Range (ESR) rule, and the exact architectural requirements for Covered Queries (totalDocsExamined: 0).

---

Compound Index Architecture & Key Ordering

A Compound Index is an index that references two or more fields within documents in a collection. MongoDB supports compound indexes containing up to 32 fields.

Syntax

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

The Importance of Field Order

The order of fields listed in a compound index definition is paramount. The B-tree orders its keys primarily by the first field (customer_id). For documents with identical values in the first field, the B-tree orders entries by the second field (status). For documents matching on both the first and second fields, entries are ordered by the third field (order_date).

Compound Index: { customer_id: 1, status: 1, order_date: -1 }

B-Tree Leaf Entries (Ordered Hierarchy):
  Key 1: [ customer_id: 101,  status: "A",  order_date: 2026-09-02 ] -> RecordId_1
  Key 2: [ customer_id: 101,  status: "A",  order_date: 2026-09-01 ] -> RecordId_2
  Key 3: [ customer_id: 101,  status: "B",  order_date: 2026-08-30 ] -> RecordId_3
  Key 4: [ customer_id: 102,  status: "A",  order_date: 2026-09-02 ] -> RecordId_4

---

Multi-Field Sort Directionality & The Inversion Principle

While single-field indexes can be traversed backward or forward to satisfy any single-field sort, compound indexes require strict sort direction compatibility when sorting on multiple fields.

The Mathematical Inversion Principle

A compound index supports sort operations if the sort pattern matches the index key pattern OR matches the exact mathematical inverse of the index key pattern (multiplying all sort directions by $-1$).

Suppose a collection has a compound index defined as: $\text{Index: } \{ a: 1, b: -1 \}$

[!WARNING] In-Memory Sort Threshold: When a query cannot obtain its sort order directly from an index, MongoDB must load all matching documents into an in-memory buffer to execute a blocking SORT stage. If the memory required for this sort exceeds 100 MB (or 32 MB in older releases), the query aborts with an execution error unless { allowDiskUse: true } is specified.

---

The Equality-Sort-Range (ESR) Rule

The Equality-Sort-Range (ESR) Rule is the industry-standard architectural guideline for structuring compound indexes to achieve maximum query performance. When formulating a compound index for a query that includes equality filters, sort specifications, and range filters, place fields in the index in the following exact order:

$\Huge \mathbf{E} \longrightarrow \mathbf{S} \longrightarrow \mathbf{R}$

1. E — Equality Fields First: Fields tested for exact equality ($eq, scalar equality matches) must appear first. This instantly narrows the B-tree scan space to a tight, contiguous block of matching index entries.
2. S — Sort Fields Second: Fields specified in the .sort() clause must appear next. Because the equality condition has pinned down a single contiguous slice of the B-tree, traversing the subsequent sort field in the index returns documents in naturally sorted order, completely eliminating the expensive in-memory SORT stage.
3. R — Range Fields Last: Fields evaluated using range comparisons ($gt, $gte, $lt, $lte, $in, regex) must appear last. Once the query engine enters a range scan on an index key, the physical order of any subsequent fields in the index is fragmented, preventing them from supporting sorted order without an in-memory sort.

Practical Case Study: Step-by-Step ESR Application

Consider an e-commerce order management query:

db.orders.find({
  status: "SHIPPED",             // Equality condition (E)
  total_amount: { $gte: 150.00 }   // Range condition (R)
}).sort({
  order_date: -1                 // Sort condition (S)
});

Evaluating Candidate Index Structures

// Constructing the optimal index following the ESR rule:
db.orders.createIndex({
  status: 1,        // Equality (E)
  order_date: -1,   // Sort (S)
  total_amount: 1   // Range (R)
});

---

Covered Queries: Achieving totalDocsExamined: 0

A Covered Query is the pinnacle of query performance in MongoDB. A query is covered when MongoDB can satisfy the entire query filter, sort criteria, and return projection exclusively from the index keys stored in memory, without fetching a single document from disk or the collection data files.

Requirements for a Covered Query

To achieve a covered query, all three conditions must be met:

1. Every field in the query filter is indexed.
2. Every field returned in the projection is indexed.
3. The primary key _id is explicitly suppressed in the projection (_id: 0), unless _id is one of the indexed fields.
4. No indexed field in the query is an array (multikey indexes cannot cover queries over array elements).

Example: Constructing and Verifying a Covered Query

// Step 1: Create a compound index on users
db.users.createIndex({ department: 1, email: 1, status: 1 });

// Step 2: Execute a covered query
const explainStats = db.users.find(
  { department: "Engineering", status: "active" },  // Filter fields in index
  { _id: 0, email: 1, department: 1 }               // Projected fields in index + suppressed _id
).explain("executionStats");

// Step 3: Inspect execution metrics
print("Docs Examined:", explainStats.executionStats.totalDocsExamined); // 0
print("Keys Examined:", explainStats.executionStats.totalKeysExamined); // Matches nReturned
print("Stage:", explainStats.executionStats.executionStages.stage);     // "PROJECTION_COVERED"

Explain Plan Signature of a Covered Query

• totalDocsExamined: 0: The storage engine never touched the underlying collection data pages.
• stage: "PROJECTION_COVERED" or IXSCAN directly feeding the client projection without a FETCH stage.
• Result: Sub-millisecond response times, zero collection disk I/O, and maximum concurrent read throughput.

Standard Query Flow:   [ IXSCAN ] === RecordId ===> [ FETCH (Disk/Cache) ] ===> Client

Covered Query Flow:    [ IXSCAN (Index Keys) ] ===> [ PROJECTION_COVERED ] ===> Client
                       (totalDocsExamined: 0 - Zero Collection Page I/O)