How does the MongoDB query optimizer choose an index?
See how MongoDB's query optimizer races candidate index plans, caches the winner by query shape, and re-evaluates when data or indexes change.
Expected Interview Answer
The MongoDB query optimizer chooses an index by generating candidate query plans for the eligible indexes, running them in parallel over a short trial, and picking the one that returns results or reaches its goal with the least work; it then caches that winning plan for similar queries.
For a new query shape, the optimizer identifies indexes whose keys can satisfy the filter and sort, builds a candidate plan for each plus a collection scan, and races them until one wins by producing results fastest with the fewest documents examined. The winner is stored in the plan cache keyed by the query shape, so later matching queries skip the race. The cache entry is evicted when statistics change, indexes are added or dropped, or performance degrades, triggering a re-evaluation.
- Adapts to real data distribution instead of static rules
- Caches winning plans so repeated queries are fast
- Automatically re-plans when indexes or data change
- Falls back to a collection scan when no index helps
- Lets developers inspect and override choices with explain() and hint()
AI Mentor Explanation
A captain choosing a bowler tries a few options in the nets under match-like conditions, watches who takes wickets with the fewest deliveries, and then keeps using that bowler while conditions hold. MongoDB races candidate index plans, picks the one that finds results with the least work, and reuses it until the pitch — the data — changes enough to warrant a fresh trial.
Step-by-Step Explanation
Step 1
Parse the query shape
The optimizer normalizes the filter, projection, and sort into a query shape used as the plan-cache key.
Step 2
Enumerate candidate indexes
It selects indexes whose keys can serve the filter and sort, plus a collection scan fallback.
Step 3
Build candidate plans
Each eligible index becomes a candidate query plan with its own access pattern.
Step 4
Race the plans
Candidates run in parallel over a trial period; the one returning results with least work wins.
Step 5
Cache the winner
The winning plan is stored in the plan cache keyed by the query shape.
Step 6
Re-evaluate when needed
The cached plan is dropped on index changes, data drift, or degraded performance, triggering a new race.
What Interviewer Expects
- Understanding that plan selection is empirical, not purely rule-based
- Knowledge of the plan cache and query shapes
- Awareness of the parallel trial (plan race)
- Knowing when plans are evicted and re-evaluated
- Familiarity with explain() and hint() for diagnosis and override
Common Mistakes
- Assuming MongoDB always uses the newest or largest index
- Believing plan choice is a fixed cost-based formula only
- Ignoring the plan cache when explaining repeated-query behavior
- Forgetting a collection scan is always a candidate
- Not using explain() to verify which index actually ran
Best Answer (HR Friendly)
“MongoDB tries out the possible indexes for a query, briefly runs them side by side, and keeps the one that finds the answer with the least effort. It remembers that choice for similar queries and re-checks it when the data or indexes change.”
Code Example
// See which plan the optimizer chose and the rejected candidates
db.orders.find({ status: 'shipped', region: 'EU' })
.sort({ createdAt: -1 })
.explain('executionStats');
// Force a specific index if the chosen plan is suboptimal
db.orders.find({ status: 'shipped', region: 'EU' })
.hint({ status: 1, region: 1, createdAt: -1 });
// Inspect and clear the cached plan for a collection
db.orders.getPlanCache().list();
db.orders.getPlanCache().clear();Follow-up Questions
- What is a query shape and how does it relate to the plan cache?
- How does the plan race decide a winner?
- When is a cached query plan evicted?
- How do you read the winningPlan and rejectedPlans in explain output?
- What is an index prefix and why does it affect eligibility?
MCQ Practice
1. How does MongoDB primarily decide between candidate indexes for a new query shape?
The optimizer races candidate plans over a trial and caches the one that returns results with the least work.
2. What is used as the key for a plan-cache entry?
Plans are cached per query shape, so queries with the same structure reuse the cached winner.
3. Which command forces MongoDB to use a specific index?
hint() overrides the optimizer and forces the query to use the specified index.
Flash Cards
How does MongoDB pick an index? — It races candidate plans in parallel and caches the one that reaches results with the least work.
What keys the plan cache? — The query shape — the structure of the filter, sort, and projection.
When is a cached plan re-evaluated? — On index add/drop, data drift, or when performance degrades.
How do you override the optimizer's index choice? — Use hint() to force a specific index.