Optimizing MongoDB Indexes at Scale

When a collection contains five thousand documents, almost any query returns in under fifteen milliseconds, even without indexes. But as datasets expand to millions of records across multi-tenant applications, unindexed queries cause database CPU to saturate, connection pools to fill, and application response times to degrade.

Database optimization requires aligning index structures with exact application access patterns rather than adding indexes indiscriminately.

Diagnosing Queries with explain("executionStats")

The first step in resolving slow database operations is inspecting the query execution plan:

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

When reviewing the output, check the stage parameter. A COLLSCAN indicates that MongoDB inspected every document in the collection; the query should instead show IXSCAN (Index Scan) followed by FETCH. In an efficient query, the ratio of totalDocsExamined to nReturned should remain close to 1.0.

The ESR Rule for Compound Indexes

When constructing compound indexes that combine equality filters, sorting criteria, and range conditions, the sequence of fields in the index key determines whether MongoDB can execute the query without an in-memory sort:

1. EQUALITY  -> Exact match fields (tenantId, status, userId)
2. SORT      -> Order-by fields (createdAt, totalAmount)
3. RANGE     -> Range filters ($gte, $lt, $in)

// Correct ESR compound index structure
db.orders.createIndex({
  tenantId: 1,      // 1. Equality
  totalAmount: -1,   // 2. Sort
  createdAt: 1      // 3. Range
})

If a range field precedes a sort field in the index definition, MongoDB cannot traverse the index tree for sorting and falls back to an in-memory sort stage, which fails if the working dataset exceeds 32MB.

Partial Indexes for Memory Efficiency

Indexes reside in RAM inside the WiredTiger cache. In collections where the majority of documents represent archived or soft-deleted records, indexing the entire collection wastes memory. Partial indexes restrict the index tree to relevant active documents:

db.subscriptions.createIndex(
  { userId: 1, expiresAt: 1 },
  { partialFilterExpression: { status: "active" } }
)

Applying the ESR rule to high-frequency query paths and using partial indexes for filtered datasets reduced average database query latency from 850ms to sub-12ms across our shared cluster.