C100DBA Indexing and Performance Practice Question
Exhibit
{
"queryPlanner": {
"winningPlan": {
"stage": "SORT",
"sortPattern": { "created": -1 },
"inputStage": {
"stage": "IXSCAN",
"indexName": "status_1"
}
}
}
}Refer to the exhibit. The explain output shows a SORT stage wrapping an IXSCAN stage. What is the performance implication of this execution plan?
⚠ Common exam trap
Students often assume that any index usage guarantees optimal performance, overlooking how a SORT stage wrapping an IXSCAN reveals that the index failed to cover the requested sort order.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The query engine performed an in-memory sort because the index did not support the requested sort order.
When a SORT stage wraps an IXSCAN stage, it means the index used for filtering did not satisfy the sort requirement. The query engine had to load the matching documents into memory to sort them, risking memory limit exceptions on large result sets.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The query is fully optimized because it leverages an index scan for filtering records.
Why it's wrong here
While the IXSCAN stage successfully filters records using the status index, the presence of an outer SORT stage indicates that the index could not order the results. This necessitates an in-memory sorting operation, which is suboptimal.
- ✓
The query engine performed an in-memory sort because the index did not support the requested sort order.
Why this is correct
The status index only supports filtering on status. Because the query also requested a sort on created, the database had to sort the resulting documents in memory, which can exceed memory limits and degrade overall execution speed.
- ✗
The query executed a full collection scan across all cluster nodes.
Why it's wrong here
IXSCAN confirms an index was used, so no COLLSCAN occurred, and MongoDB sharded clusters distribute collections across shards rather than scanning every node for this plan. The option tempts when index selection fails, which would show COLLSCAN instead of IXSCAN in the explain output.
- ✗
The storage engine automatically converted the query into a covered query.
Why it's wrong here
A SORT stage above IXSCAN means the index supplied sort order was insufficient, so the engine sorts results in memory after fetching documents. Covered queries eliminate the FETCH stage instead, projecting all fields from the index itself; the plan shows no such projection, so no automatic conversion occurred.
About these practice questions
This C100DBA question is part of Courseiva's 222-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official MongoDB exam blueprint
This C100DBA practice question is part of Courseiva's free MongoDB certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the C100DBA exam.