Why MongoDB Scans 10,000 Documents to Return One and How to Fix It

I got an email from MongoDB about a query that was scanning 1,000 documents to return one.

That did not sit right. One result should not need that much work. So I went into the rabbit hole of explain("executionStats"), collection scans, compound indexes, and sort stages until the gap between nReturned and totalDocsExamined made sense.

This post is what I learned. The examples below use a simplified orders collection (customerId, status, createdAt) and a 10,000-document scale so the numbers are easy to follow. Same query shape, different plans, and a few small playgrounds so you can see the difference instead of only reading about it.

// MongoDB 8.0, in mongosh
db.orders.find({ customerId: "c-42", status: "paid" })
  .explain("executionStats")

What the numbers actually mean

Explain output has a lot of fields. These are the ones I check first in explain("executionStats"):

• nReturned: how many results came back.
• totalDocsExamined: how many documents were examined, including ones that failed the filter.
• totalKeysExamined: how many index entries were walked.
• executionTimeMillis: time for plan selection + execution.

10,000 examined for 1 returned is a strong clue that something is off. It does not prove the query is slow on your machine. Collection size, what's in memory, hardware, and how often it runs all matter. Also check totalKeysExamined. An index can walk many keys while fetching little that is useful.

Full definitions live in the MongoDB 8.0 explain reference.

Collection scan vs index lookup

Without a useful index, you get a COLLSCAN. MongoDB walks documents and checks each one against the filter. Our query has no limit, so on a collection of 10,000 orders it can examine all of them for one match.

On a small collection, that query looks harmless: a few hundred documents, response still feels fine, nothing in the app feels wrong. As the collection grows, the same plan examines more and more documents for the same one result, and the work grows with the collection. What felt fine in early data becomes expensive in production.

An index is a sorted lookup list. Think of a book index: instead of reading every page to find "MongoDB", you jump to the right entry, then open that page.

With { customerId: 1, status: 1 }, MongoDB keeps a sorted list that looks roughly like:

(c-09, paid)  → pointer to document o-103
(c-17, paid)  → pointer to document o-101
(c-42, paid)  → pointer to document o-105
(c-42, pending) → pointer to document o-102
...

For find({ customerId: "c-42", status: "paid" }), MongoDB does two things. You will see both names in explain:

1. IXSCAN: search that sorted list and find the matching key (c-42, paid). This is cheap because the list is ordered. It does not read every order in the collection.
2. FETCH: follow the pointer and load the full order document (o-105), because the index only stores customerId and status. Your app usually also needs fields like createdAt, _id, amounts, and so on. Those live on the document, not in this index.

So: IXSCAN finds which documents match. FETCH loads those documents.

If the query only asked for fields already in the index (a covered query), MongoDB could skip FETCH and answer from the index alone. Most app queries are not covered, so FETCH is normal.

One more caveat: seeing IXSCAN does not mean the query is fast. If the index matches a huge range (say half the collection), MongoDB still walks many keys and fetches many documents. Better than a full collection scan, but still a lot of work.

Try the lab below: collection scan vs compound index.

More on compound indexes and explain stages.

Read the real plan

Run explain on the query your app actually sends: filter, projection, sort, and limit included. Not a simplified version typed into mongosh.

db.orders.find({ customerId: "c-42", status: "paid" })
  .explain("executionStats")

Look at queryPlanner.winningPlan, then the counters in executionStats.

• COLLSCAN: examining collection documents
• IXSCAN: reading index keys
• FETCH: reading documents
• SORT: sorting candidates (often where cost shows up)

MongoDB 8.0 can use different execution engines, so the plan tree might not look identical across systems. Read the stages and their work rather than expecting one fixed JSON shape.

index: none suitable
query: { customerId: "c-42", status: "paid" }
plan:  COLLSCAN

// examine every document in the collection

That comparison assumes one matching order among 10,000. Illustrative numbers: same query, very different amount of work.

Field order in a compound index

{ customerId: 1, status: 1 } and { status: 1, customerId: 1 } have the same fields. They are not the same index. Order defines which queries the index can serve efficiently.

Both can help the exact two-field lookup above. Their prefixes help different other queries:

• { customerId: 1, status: 1 } starts with customerId → good for "all orders for c-42"
• { status: 1, customerId: 1 } starts with status → good for "all paid orders"

An index generally cannot use a missing leading field as an efficient prefix. Also: a lone { customerId: 1 } index can be redundant next to { customerId: 1, status: 1 } unless uniqueness, sparsity, or another constraint needs it. Check your other queries before dropping an index. Compound index prefixes cover the rule.

For queries with equality + sort + range, start with Equality-Sort-Range. Equality fields first, then sort, then range. If a range cuts the set down hard, putting it before the sort field can win even if MongoDB has to sort afterward. The ESR guideline explains the tradeoff.

Sorting changes the work

Now we want the three newest paid orders:

db.orders.find({ status: "paid" })
  .sort({ createdAt: -1 })
  .limit(3)
  .explain("executionStats")

An index on { status: 1 } finds paid orders. It does not order them by createdAt. So MongoDB still needs a SORT stage.

With { status: 1, createdAt: -1 }, paid orders are already newest-first. Equality on status, ordered keys, take three, stop.

.limit(3) limits results, not necessarily how many candidates get examined. A blocking sort still has to consider the matching set before picking the top three. For compound sorts, directions must match the index (or its full reverse). MongoDB docs: index-supported sorts and compound sort direction.

Don't index everything

A filter on a value almost every document shares (say status: "paid" in a mostly-paid collection) is not selective. An index on that field may still scan and fetch most of the collection. MongoDB may choose a collection scan instead. Their selectivity guidance is worth reading.

The workflow I use

1. Copy the real query shape and representative values from the app.
2. Run explain("executionStats"). Check winning stages. Compare returned vs examined docs vs keys. Look for a broad scan or a SORT.
3. Pick an index that fits this query and the other important ones. Consider prefix order, and whether sort or a selective range hurts more.
4. Explain again on representative data. Compare work counters. Check real app latency separately. Explain is not a stopwatch.
5. Look at storage, writes, and other queries before keeping the change. Drop redundant indexes only after checking use and constraints.

A result count tells you what came back. The plan tells you what MongoDB had to examine to get it.

For a deeper dive on diagnosing slow queries, read MongoDB's guide: Your Guide to Diagnose Slow Queries in MongoDB.

-- bye for now