Study guide
5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule
MongoDB Associate Developer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Document Model & MongoDB Architecture
1.1 Document Model Fundamentals & BSON Data Types 1.2 Databases, Collections & Schema Flexibility 1.3 Distributed Architecture Fundamentals: Replica Sets & Sharded Clusters
2 Chapter 2: CRUD Operations: Create & Read
2.1 Document Insertion Operations 2.2 Query Selectors & Filter Operators 2.3 Array Queries & Element Evaluation 2.4 Projections, Cursor Methods & Collation
3 Chapter 3: CRUD Operations: Update & Delete
3.1 Field Update Operators 3.2 Array Update Operators & Positional Modifiers 3.3 Document Replacement & Deletion Operations 3.4 Atomic Find-and-Modify Operations
4 Chapter 4: Aggregation Framework Mastery
4.1 Pipeline Fundamentals & Core Stages ($match, $project, $addFields) 4.2 Grouping, Reshaping & Array Manipulation ($group, $unwind, $replaceWith) 4.3 Multi-Document Joins & Faceted Pipelines ($lookup, $facet, $bucket) 4.4 Materialization & Window Operations ($out, $merge, $setWindowFields) 4.5 Atlas Search: Search Index Definitions & $search Queries
5 Chapter 5: Indexing Strategies & Performance Optimization
5.1 Index Types & Storage Mechanics 5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule 5.3 Multikey Indexes & Array Indexing Constraints 5.4 Specialized Index Types: TTL, Partial, Unique & Text 5.5 Query Optimization & Explain Plan Analysis
6 Chapter 6: Data Modeling & Schema Design Patterns
6.1 Embedding vs. Referencing Tradeoffs 6.2 Core Schema Design Patterns 6.3 Schema Validation with JSON Schema
7 Chapter 7: Application Development & Driver Integration
7.1 Connection Strings & Connection Pool Management 7.2 Driver CRUD Paradigms & Object Mapping 7.3 Cursor Batching, Streaming & Memory Handling 7.4 Write Concerns, Read Concerns & Retryable Operations
8 Chapter 8: Developer Tooling & Administration Essentials
8.1 mongosh Scripting & Administrative Commands 8.2 Data Ingestion & Export Utilities 8.3 Backup, Restore & Performance Telemetry 8.4 Atlas Sample Dataset & Data Explorer
5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule
Key Takeaways Explain This Topic AI
A compound index holds references to up to 32 fields, with key order dictating the hierarchical sort structure of the B-tree.
In multi-field sorting, a compound index on { a: 1, b: -1 } supports sorts on { a: 1, b: -1 } and its exact mathematical inverse { a: -1, b: 1 }.
The ESR (Equality, Sort, Range) Rule dictates placing exact equality fields first (E), sort fields second (S), and range filter fields last (R) to avoid in-memory sorting.
Placing a range field before a sort field in a compound index prevents index-backed sorting, forcing MongoDB into an in-memory SORT stage capped at 100 MB.
A Covered Query occurs when all filter, sort, and projected fields exist within the index and '_id: 0' is projected out, resulting in totalDocsExamined = 0.
Last updated: September 2026
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 -1 − 1 ).
Suppose a collection has a compound index defined as: Index: { a : 1 , b : − 1 } \text{Index: } \{ a: 1, b: -1 \} Index: { a : 1 , b : − 1 }
- Requested Sort Pattern Compatible with Index { a: 1, b: -1 } ? Traversal Mechanics & Outcome
- .sort({ a: 1, b: -1 }) ✅ Yes Forward index traversal (Direct match)
- .sort({ a: -1, b: 1 }) ✅ Yes Backward index traversal (Exact mathematical inverse)
- .sort({ a: 1, b: 1 }) ❌ No Incompatible; forces in-memory SORT stage
- .sort({ a: -1, b: -1 }) ❌ No Incompatible; forces in-memory SORT stage
- .sort({ a: 1 }) ✅ Yes Uses index prefix { a: 1 } via forward scan
- .sort({ a: -1 }) ✅ Yes Uses index prefix { a: 1 } via backward scan
- .sort({ b: 1 }) ❌ No b is not a prefix; cannot provide sort order
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: E ⟶ S ⟶ R \Huge \mathbf{E} \longrightarrow \mathbf{S} \longrightarrow \mathbf{R} E ⟶ S ⟶ R
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.
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.
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
Candidate Index Pattern ESR Compliance Query Execution Behavior & Explain Plan Analysis
{ status: 1, order_date: -1, total_amount: 1 } ✅ Optimal (E-S-R) No In-Memory Sort: Jumps directly to status: "SHIPPED" , scans index in order_date descending sequence, tests total_amount >= 150.00 on index keys, fetches only matching docs.
{ status: 1, total_amount: 1, order_date: -1 } ❌ Suboptimal (E-R-S) In-Memory Sort Stage: Evaluates range on total_amount first. Because range values vary, order_date entries are interleaved; MongoDB is forced to execute a blocking SORT stage in RAM.
{ total_amount: 1, status: 1, order_date: -1 } ❌ Poor (R-E-S) Massive Key Scan + In-Memory Sort: Scans broad range of total_amount across all statuses, discards non-matching status keys, and requires an in-memory sort. // 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:
Every field in the query filter is indexed.
Every field returned in the projection is indexed.
The primary key _id is explicitly suppressed in the projection ( _id: 0 ), unless _id is one of the indexed fields.
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)