MongoDB Indexing Basics: Fixing Slow Queries

A missing or poorly-matched index is a common cause of a slow MongoDB query — but not the only one; query shape, poor selectivity, a sort MongoDB can’t satisfy from an index, and the overall result volume all matter too. Confirming which one is actually at play, rather than guessing, is the point of explain().

Confirm it first: explain()

Before adding an index, run the query through .explain("executionStats") and check the stage field:

db.orders.find({ customerId: "abc123" }).explain("executionStats");

• COLLSCAN — a full collection scan; every document gets examined. This is usually what an index fixes, though on a genuinely small collection a collection scan may already be fast enough that adding an index wouldn’t change much in practice.
• IXSCAN — an index scan. The query is using an index, but that alone doesn’t guarantee it’s an efficient one — check totalKeysExamined (how many index entries were scanned) alongside totalDocsExamined and nReturned, since an index scan that still touches far more keys than it returns points at a poorly-matched index, not a solved problem.

The totalDocsExamined/totalKeysExamined vs. nReturned gap tells the story numerically: examining 500,000 documents to return 12 is the signature of a missing or poorly-matched index.

Adding the index

db.orders.createIndex({ customerId: 1 });

The 1 means ascending order — for a single-field equality filter like this, direction rarely matters. Creating the index makes it available; it doesn’t force its use — the query planner chooses the winning plan, and for an equality filter on an indexed field it will generally pick it, but re-running explain() is how to confirm that actually happened, rather than assuming it from the index existing. Look for IXSCAN with totalKeysExamined/totalDocsExamined close to nReturned.

// Before (no index) — abbreviated executionStats
{ stage: "COLLSCAN", nReturned: 12, totalDocsExamined: 500000, totalKeysExamined: 0 }

// After createIndex({ customerId: 1 })
{ stage: "IXSCAN", nReturned: 12, totalDocsExamined: 12, totalKeysExamined: 12 }

Compound indexes: field order matters

When a query filters or sorts on multiple fields, a compound index needs its fields in the right order — specifically, equality filters first, then sort fields, then range filters (the “ESR rule”: Equality, Sort, Range).

// Query: filter by status (equality), sort by createdAt
db.orders.find({ status: "completed" }).sort({ createdAt: -1 });

// Matching compound index
db.orders.createIndex({ status: 1, createdAt: -1 });

An index on { createdAt: -1, status: 1 } — same fields, reversed order — can still support the sort on createdAt, but it’s less suited to narrowing down by status first, so filtering tends to examine more index entries than the { status: 1, createdAt: -1 } version would. ESR (Equality, Sort, Range) is the right starting rule of thumb, not an absolute one — when a query’s range predicate is highly selective (it narrows the result set sharply on its own), an ESR-vs-ERS ordering swap can sometimes perform better; explain() on the actual query and data is what settles it, not the rule alone.

The tradeoff: indexes aren’t free

Every index speeds up the reads it matches but adds write-side cost to that collection, since each insert/update also has to update every index whose fields it touches — a write only pays for the indexes actually affected, but a collection with ten broad indexes “to be safe” still adds up fast. A collection with that many is a common cause of slow write throughput, not a solution. The practical approach: index for the queries the application actually runs — found by checking logs or explain() on real slow queries — not preemptively for every field that might someday be filtered.

A quick checklist

1. Confirm the slow query with .explain("executionStats") — don’t guess at the cause.
2. Add an index matching the fields actually filtered/sorted on.
3. For multi-field queries, start with the ESR rule for field order, but verify with explain() rather than treating it as absolute.
4. Re-run explain() and confirm the execution stats actually improved — IXSCAN replacing COLLSCAN is the usual signal, but check totalKeysExamined/totalDocsExamined vs. nReturned too, not just the stage name.
5. Don’t index fields with no query backing it — each index has a write-side cost.

See also: MongoDB Aggregation Pipeline: A Practical Introduction for applying the same $match-first logic inside a pipeline, and 10 Online Code Playgrounds for React, Python & More for a sandbox to run explain() without a local MongoDB install.