If a query runs fine at 10,000 documents and becomes slow at 100,000, the most likely cause is a missing index — not a MongoDB bug, not a schema problem, not a driver issue. MongoDB’s query planner defaults to a full collection scan (COLLSCAN) when no suitable index exists. That scan touches every document in the collection regardless of how selective the filter is. Understanding how MongoDB builds and uses indexes is the operational knowledge that separates a collection that stays fast from one that degrades linearly with data volume.

Situation

Engineers moving to MongoDB from a relational background often expect the optimizer to behave like PostgreSQL or MySQL: add a column and the planner will figure the rest out. MongoDB does use indexes when they exist — but there is no implicit index creation. Without an explicit index on a field, every query that filters, sorts, or aggregates on that field will scan the entire collection.

The rate of degradation is what surprises engineers: a COLLSCAN at 10K documents takes milliseconds; the same scan at 1M documents takes seconds. The collection felt fast during development because the data volume was too small for the problem to be visible.

The Problem

The failure mode is predictable: somewhere between 50K and 200K documents, a query that returns a single record starts taking seconds. The engineer adds an index — but adds it on the field they notice in the filter, not on the field the planner needs. Latency improves slightly or not at all. The problem is that they did not know how to read the query planner output, and they did not understand how compound index ordering affects whether an index can be used for both filtering and sorting. The core question: given a query with a filter, a sort, and a range condition, how do you build an index the planner will actually use?

How MongoDB Indexes Work

MongoDB uses B-tree indexes on individual fields or combinations of fields. Three index types matter for most applications.

Single-field indexes are the starting point. An index on { status: 1 } lets the planner use IXSCAN for any query filtering on status. If your query also sorts on createdAt, the index handles the filter but leaves the sort as an in-memory operation — and if that result set exceeds 32MB, MongoDB aborts the sort with an error.

Compound indexes cover multiple fields in a declared order. The order matters because of the prefix rule: an index on { status: 1, userId: 1, createdAt: -1 } supports queries on status, on status + userId, and on all three. It does not support a query filtering only on userId — the prefix must be respected.

For compound indexes that involve both equality filters, sort conditions, and range filters, MongoDB’s documentation describes the ESR rule as the recommended ordering: Equality fields first, then Sort fields, then Range fields. The rationale is mechanical: placing equality conditions first narrows the index scan to exact key matches before any range traversal or sort is applied. Putting a range field before the sort field forces the planner to sort within a wider range, which can make in-memory sorting unavoidable even when the index exists. The ESR rule is documented in the MongoDB manual under “Create Indexes to Support Your Queries.”

Multikey indexes handle array fields. If a document has a field tags: ["mongodb", "indexes", "performance"], an index on { tags: 1 } creates one index entry per array element. Queries for any single tag value use IXSCAN. The constraint is that a compound index cannot have two multikey fields: MongoDB will reject index creation on { tags: 1, categories: 1 } if both are array fields in the same document.

The diagnostic tool is explain(). Appending .explain("executionStats") returns the plan the planner chose. The critical fields: winningPlan.stage (IXSCAN versus COLLSCAN), executionStats.totalDocsExamined versus executionStats.nReturned (a large ratio means poor selectivity or the wrong index), and executionStats.executionTimeMillis.

db.orders.find({ status: "pending", userId: "u123" })
         .sort({ createdAt: -1 })
         .explain("executionStats")

COLLSCAN means no index supports the query. IXSCAN with totalDocsExamined far exceeding nReturned means the index exists but the wrong fields or order were used.

In Practice

MongoDB’s documentation covers the ESR rule and its rationale in the “Indexing Strategies” section of the manual. The prefix rule for compound indexes follows directly from how WiredTiger (MongoDB’s default storage engine since 3.2) walks the B-tree key space — behavior documented in the WiredTiger storage engine reference. The documented diagnostic pattern is: run explain("executionStats"), confirm IXSCAN versus COLLSCAN, check totalDocsExamined against nReturned, and verify the compound index matches the ESR order for the query’s filter, sort, and range fields. This behavior has been consistent across MongoDB versions since 3.x.

Where It Breaks

ScenarioWhat breaksWhy
Two array fields in a compound indexIndex creation is rejected with a MongoServerErrorWiredTiger cannot create a compound multikey index across two array fields — the cardinality expansion is unbounded
Low-cardinality field as the leading equality keyIndex exists but does not improve performance meaningfullyA field with five distinct values produces large index buckets; the planner scans a large fraction of the index even with IXSCAN
Sort on a field not in the indexIn-memory sort is triggered; aborts if the result set exceeds 32MBWhen the sort field is absent from the index, the planner cannot use the index ordering and must buffer and sort the result in memory

What to Do Next

  • Problem: A MongoDB collection that performs acceptably at development scale will degrade to COLLSCAN latency in production if indexes are not built to match query shapes.
  • Solution: Run .explain("executionStats") on every slow query, verify the winning plan uses IXSCAN, then build or rebuild compound indexes following the ESR rule — equality fields first, sort fields second, range fields last.
  • Proof: After adding the correctly ordered compound index, re-run explain("executionStats") and confirm winningPlan.stage shows IXSCAN and totalDocsExamined drops to match nReturned.
  • Action: This week, run .explain("executionStats") on the three slowest queries in your application and check whether any of them are using COLLSCAN.

The query planner cannot use an index it was not given. Once you can read explain() output, the path from slow query to correct index is mechanical.