Index Performance | MongoDB Indexing Tutorial

Key Takeaways

• A suitable index reduces query work; it doesn't guarantee constant latency as your collection grows.
• Start compound-index design with Equality, Sort, Range (ESR), but compare Equality, Range, Sort (ERS) when the range is highly selective.
• Measure documents returned, keys examined, documents examined, and blocking sorts—not just whether an index appears in the plan.
• Balance read improvements against index storage, write maintenance, and the memory demands of your active working set.

MongoDB index performance improves when an index lets a query examine less data or return results in the requested order without a separate sort. The practical starting point is a compound index ordered by Equality, Sort, Range—but the right index depends on your query shape, data distribution, and workload costs.

How MongoDB Indexes Reduce Query Work

An ordinary MongoDB index stores ordered index keys and references to documents. Rather than inspecting every document, MongoDB can use those keys to locate candidates, evaluate some conditions, and sometimes supply the requested ordering. The MongoDB indexing overview explains these access paths.

What's a B-Tree?

MongoDB's ordered indexes use a B-tree, a balanced search-tree structure. In the usual B-tree cost model, locating the beginning of a range takes logarithmic work as the index grows; scanning the range then adds work proportional to how much of it must be read. A broad range can therefore remain expensive even with an index.

Logical tree traversal isn't the same as physical disk I/O. Pages may already be cached, and fetching matching documents can require additional page reads. There's no general guarantee that a document is three or four I/O operations away, or that indexed-query latency stays unchanged as your data grows.

The ESR Guideline for Compound Indexes

ESR means Equality, Sort, Range: place exact-match fields first, followed by fields supplying result order, then range fields. It's a starting point, not a universal optimum. If compound keys are new to you, read the companion Compound Index tutorial alongside MongoDB's ESR guideline.

Query: tenantId = value, status = value, total >= threshold
Sort:  createdAt descending

ESR: [tenantId, status] -> [createdAt DESC] -> [total]
      equality             sort                range

ERS: [tenantId, status] -> [total] -> [createdAt DESC]
      equality             range      sort

Equality: Fix the Leading Keys

An equality predicate matches one exact value, such as tenantId: "tenant-3". With equality constraints on the leading index keys, MongoDB can work within that combination of values rather than across the entire index. Equality doesn't automatically mean high selectivity: a common status value can match much of a collection.

When every equality predicate is present, equality fields can appear in either order relative to one another. Choose their order partly for other queries: putting tenantId first also provides a tenant-only index prefix.

Sort: Creating an Index Isn't Requesting Sorted Results

db.collection.createIndex({name: -1})
db.collection.find({}).sort({name: -1})

The first command creates a descending index on name; it doesn't return sorted documents. The second explicitly requests descending results. A single-field index can support sorting in either direction through forward or reverse traversal. See the Create Index tutorial for index-creation syntax.

For compound indexes, sort support depends on field order and direction. After fixing preceding keys with equality conditions, the requested sort keys must follow the index's order and match their indexed directions or the complete reverse. A matching filter alone doesn't prove that the index can supply the sort.

Range: Decide Whether Avoiding a Sort Is Worth More Scanning

A condition such as total: { $gte: 950 } matches a range rather than one value. Putting the range after the sort field preserves the useful ordering within the equality prefix. Putting it before the sort field can narrow the scan more effectively, but generally requires a separate sort for this query.

Choose ESR when avoiding that sort matters most. Test ERS when the range eliminates most candidates and sorting the smaller result is cheaper than scanning more keys in sort order. That's a workload decision, not a rule you can settle from field names alone.

Why Compound-Index Prefixes Matter

A prefix is a consecutive set of fields starting at the beginning of an index. For { tenantId: 1, status: 1, createdAt: -1, total: 1 }, the prefixes begin with tenantId, then tenantId, status, then tenantId, status, createdAt.

A tenant-only filter can use the leading prefix. A tenant-and-status filter can also use that prefix and request results ordered by createdAt. But omitting status leaves it varying between the tenant and date keys, so the index doesn't directly provide a tenant-wide date sort. Skipping intermediate fields can also increase scanned work.

Understanding MongoDB Index Selectivity

Selectivity describes the fraction of documents matching a query: proportionally fewer matches means greater selectivity. There's no universal percentage threshold. Cardinality—the number of distinct field values—is useful context, but the frequency of the particular values you query matters too.

For example, status: "open" may be unselective when most orders are open, while an equality condition on an uncommon customer identifier may be selective. A low-selectivity index can still help with ordering or other query requirements; it simply isn't guaranteed to beat a collection scan. Evaluate the whole query, not one predicate in isolation.

Measure MongoDB Index Performance with explain()

Use explain("executionStats") to execute the query and inspect its work. The following lab compares the same filter and sort before indexing, with ESR and ERS candidates, and finally without forcing an index.

Create the Test Data and Capture the Baseline

const lab = db.getSiblingDB("stackchief_index_lab");
const orders = lab.orders;

if (orders.countDocuments({}) !== 0) {
  throw new Error("Use a fresh, empty lab database.");
}

orders.insertMany(Array.from({ length: 10000 }, (_, i) => ({
  _id: i,
  tenantId: `tenant-${i % 10}`,
  status: i % 3 === 0 ? "closed" : "open",
  total: (i * 37) % 1000,
  createdAt: new Date(Date.UTC(2026, 0, 1) + i * 60000)
})));

const filter = {
  tenantId: "tenant-3",
  status: "open",
  total: { $gte: 950 }
};
const sort = { createdAt: -1 };

function report(label, result) {
  const s = result.executionStats;
  printjson({
    label,
    nReturned: s.nReturned,
    totalKeysExamined: s.totalKeysExamined,
    totalDocsExamined: s.totalDocsExamined,
    winningPlan: result.queryPlanner.winningPlan
  });
}

report("before", orders.find(filter).sort(sort)
  .explain("executionStats"));

Compare ESR and ERS Against the Same Query

orders.createIndex(
  { tenantId: 1, status: 1, createdAt: -1, total: 1 },
  { name: "orders_esr" }
);
orders.createIndex(
  { tenantId: 1, status: 1, total: 1, createdAt: -1 },
  { name: "orders_ers" }
);

for (const indexName of ["orders_esr", "orders_ers"]) {
  report(indexName, orders.find(filter).sort(sort)
    .hint(indexName).explain("executionStats"));
}

report("planner choice", orders.find(filter).sort(sort)
  .explain("executionStats"));

The name option identifies each candidate, and hint() forces that index for the comparison. The final call removes the hint so you can inspect the planner's choice. Don't turn a diagnostic hint into an application requirement without testing how it behaves as your data changes.

Interpret the Counters and Sort Stage

In MongoDB's explain results, nReturned counts documents returned by the winning plan, totalKeysExamined counts index entries scanned, and totalDocsExamined counts document examinations—including documents subsequently rejected. These are work counters, not physical disk-read counters.

Inspect the winning plan for COLLSCAN (collection scan), IXSCAN (index scan), FETCH (document retrieval), and an explicit SORT. Plan nesting varies by execution engine, so inspect the tree rather than assuming one fixed property path.

These are expected plan properties for this scalar-field example, not measured benchmark results. A blocking sort must consume its input before producing sorted output. Verify the actual stages and counters on your server, then repeat with representative tenants, range bounds, projections, and result limits.

Does the Index Need to Fit Inside RAM?

No. MongoDB with WiredTiger uses both an internal cache and the operating system's filesystem cache. Focus on the working set: the index and document pages your workload repeatedly accesses, rather than requiring every index to remain resident in memory.

Cold or frequently evicted pages can require storage reads and increase latency. A page absent from WiredTiger's cache may still be available through the filesystem cache, however. Correlate query latency with cache eviction, storage reads, and I/O latency; index size alone doesn't diagnose a memory problem. See WiredTiger memory use for the cache model.

Balance Read Speed Against Index Costs

Indexes consume storage and add write-maintenance work. Inserts and deletes add or remove applicable index entries; updates maintain indexes affected by changed keys or index membership. More indexes can therefore improve reads while reducing write throughput and increasing cache demands.

Review the actual definitions using the List Indexes tutorial, then inspect storage and usage in the same mongosh session:

orders.stats().indexSizes
orders.aggregate([{ $indexStats: {} }])

indexSizes reports per-index sizes. $indexStats reports user-driven index use: check both accesses.ops and accesses.since. Its counters are node-local and reset after events such as a server restart or dropping and recreating an index; zero use over a short window doesn't establish that an index is unnecessary.

Check Index Eligibility Before Changing Field Order

Some problems aren't ESR problems. Partial indexes contain only documents matching a filter, so a query must imply that filter to use them without losing results. Indexes over array fields become multikey indexes, which have additional bounds and sorting restrictions.

String comparisons need the same collation—the rules for comparing strings—as the index. TTL indexes serve automatic data expiration, not just read acceleration. Keep these properties in view when evaluating apparently unused indexes; the Indexing Types tutorial covers the broader choices.

Production Checklist

• Record a baseline: capture important query shapes, parameter distributions, scan counts, application latency, and write throughput.
• Budget for the build: index builds use memory and temporary disk space and can reduce read/write performance. They take exclusive collection locks at the beginning and end; interleaving operations can proceed during the rest of the build. Follow MongoDB's index-build operational guidance.
• Validate without hints: compare candidates explicitly, then confirm ordinary queries choose acceptable plans across representative values.
• Test removal reversibly: where appropriate, hide an existing index before dropping it. Hidden indexes remain maintained and consume resources, so hiding tests planner impact—not the write savings from removal. A hidden index cannot be hinted.
• Protect non-query responsibilities: check uniqueness constraints, TTL retention, scheduled jobs, and infrequent queries before removing an index. Read-use counters alone don't capture all of those responsibilities.

Common Mistakes

• Calling a query optimized merely because its plan contains an index scan.
• Treating equality as automatically selective or ESR as automatically optimal.
• Assuming an index supplies a sort because it contains the sort field somewhere in its key pattern.
• Benchmarking one cached query value and applying the result to every tenant and range.
• Keeping both experimental candidates without measuring whether their read benefits justify their write and storage costs.

FAQ

How do I tell whether MongoDB is using an index?

Run the query with explain("executionStats") and inspect the winning plan for an index scan such as IXSCAN. Check the index name, scan counters, and any SORT stage: using an index doesn't prove the query is efficient.

What do totalKeysExamined and totalDocsExamined mean?

totalKeysExamined counts index entries scanned; totalDocsExamined counts document examinations during execution, including rejected documents and repeated examinations. Compare both with nReturned to understand how much work produces the result; neither counter measures physical disk reads.

When should I use ESR versus ERS for a compound index?

Start with ESR when supplying sort order from the index is important. Test ERS when a selective range can reduce scanned work enough to justify a separate sort, and compare both using representative data and actual result limits.

Can a MongoDB index make writes slower?

Yes. Inserts and deletes maintain applicable index entries, while updates maintain indexes affected by changed keys or membership. Additional indexes can trade faster reads for more write work and storage.

Does a MongoDB index need to fit entirely in RAM?

No. WiredTiger and the operating system cache index and document pages, so useful performance doesn't require every index to fit entirely in RAM. What matters is your active working set and the observed effect of cache misses and storage reads.

Further Reading

• MongoDB indexes overview — ordered keys and query access paths.
• The ESR guideline — equality ordering and the ESR-versus-ERS trade-off.
• Compound indexes — prefixes and field-order behavior.
• Using indexes to sort query results — directions and preceding equality constraints.
• Explain results reference — execution counters and plan stages.
• WiredTiger storage engine — internal and filesystem caching.
• $indexStats reference — usage counters and their limitations.
• Index builds on populated collections — resource usage and operational impact.