StackAdvisor: MongoDB Indexing Strategy (2026): How to Fix Slow Query Performance & ESR Rule

As NoSQL document databases scale to millions of records, unindexed or improperly indexed queries trigger full collection scans (COLLSCAN). This leads to severe I/O bottlenecks, elevated CPU usage, and application timeouts. Building effective MongoDB indexes requires understanding index data structures (B-Trees) and applying the fundamental ESR (Equality, Sort, Range) Rule.

This guide explains how to analyze slow database operations using explain(), construct high-performance compound indexes, and eliminate memory-intensive in-memory sorting operations.

Understanding the ESR (Equality, Sort, Range) Rule

When creating compound indexes to support complex queries that include filtering and sorting, the field order within the index definition determines whether MongoDB can perform a fast index scan (IXSCAN) or falls back to expensive in-memory sorts.

Order	Rule Step	Description & Index Logic
1	E - Equality	Place fields queried with exact matches (e.g., `{ status: "active" }` or `{ tenantId: 102 }`) first in the index. These narrow down the search space immediately.
2	S - Sort	Place fields used in `.sort()` operators (e.g., `{ createdAt: -1 }`) next. This allows the query engine to traverse index keys in order without performing an in-memory sort.
3	R - Range	Place fields queried with range operators (e.g., `$gt`, `$lt`, `$in`, `$gte`) last. Once a range filter is applied, subsequent index fields cannot be used efficiently for sorting.

1. Analyzing Slow Queries with `explain("executionStats")`

Run the .explain("executionStats") method in MongoDB Shell or database drivers to inspect query execution plans and identify bottleneck stages.

db.orders.find({
  status: "completed",
  totalAmount: { $gte: 100 }
}).sort({ createdAt: -1 }).explain("executionStats");

Red Flags in Explain Output

stage: "COLLSCAN": The query engine scanned the entire collection because no suitable index was available.
totalDocsExamined >> nReturned: Scanning thousands of documents to return only a few results indicates an inefficient index. Ideally, totalKeysExamined should closely match nReturned.
stage: "SORT": Indicates an in-memory sort. If the result set exceeds 32 MB, MongoDB will throw a runtime error unless allowDiskUse is enabled.

2. Code Example: Refactoring Indexes with the ESR Rule

Target Query Pattern

// Application Query
db.orders.find({
  merchantId: "m_88291",                   // Equality
  orderTotal: { $gte: 50.00 }              // Range
}).sort({ createdAt: -1 });               // Sort

❌ BAD: Incorrect Compound Index Order (Non-ESR)

// Range placed BEFORE Sort
db.orders.createIndex({ merchantId: 1, orderTotal: 1, createdAt: -1 });

Why it fails: Placing the range field (orderTotal) before the sort field (createdAt) forces MongoDB to scan all matching range documents and perform a costly blocking in-memory sort (stage: "SORT").

✅ GOOD: Correct ESR Index Construction

// Index Order: Equality (merchantId) -> Sort (createdAt) -> Range (orderTotal)
db.orders.createIndex({ merchantId: 1, createdAt: -1, orderTotal: 1 });

Why it succeeds: The database engine narrows records by merchantId, reads matching keys already sorted by createdAt from the index B-Tree, and filters by orderTotal without needing an in-memory sort stage.

Production Index Maintenance Checklist

Avoid Over-Indexing: Every index adds overhead to write operations (insertOne, updateOne, deleteOne) because index B-Trees must update synchronously on every write.
Use Partial Indexes for Sparse Data: If querying only a subset of documents (e.g., active subscriptions), create a partial index to save RAM:
```
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { isActive: true } }
);
```
Monitor Unused Indexes: Run db.collection.aggregate([{ $indexStats: {} }]) regularly in production to drop indexes with low or zero access counts.

MongoDB Indexing Strategy (2026): How to Fix Slow Query Performance & ESR Rule