C100DBA Indexing and Performance Practice Question
A DBA observes that a query with filter { a: 5, b: { $gt: 10 } } and sort { c: 1 } is using a collection scan despite the existence of an index { a: 1, b: 1, c: 1 }. The DBA wants to improve performance. Which action is most likely to allow the index to support both the filter and the sort?
⚠ Common exam trap
The trap here is assuming that a compound index can support a sort on a field that comes after a range predicate in the index key pattern.
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
✓
Create a new index { a: 1, c: 1, b: 1 } so that the sort field c comes before the range field b.
The index { a: 1, b: 1, c: 1 } cannot support the sort on c because b is a range predicate, which means the index order after the equality on a is determined by b, not c. To support both the filter and the sort, the index should be reordered so that the sort field c follows the equality field a, and the range field b comes last. The index { a: 1, c: 1, b: 1 } allows the equality on a to bound the prefix, then the sort on c to be satisfied by the index order, and finally the range on b to be applied as an index filter.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Create a new index { a: 1, c: 1, b: 1 } so that the sort field c comes before the range field b.
Why this is correct
The current index { a: 1, b: 1, c: 1 } cannot support the sort on c because b is a range predicate, which breaks the index prefix for sorting. After the equality on a, the next key is b, but b is not an equality; it is a range. The index can use a and b for filtering, but the sort on c cannot be satisfied because the index order after b is c, but b varies. By reordering the index to { a: 1, c: 1, b: 1 }, the equality on a is followed by the sort key c, allowing the index to provide sorted results. The range on b can then be applied as an index filter on the remaining key. This is the correct approach.
- ✗
Create a partial index on { a: 1, b: 1, c: 1 } with a filter on b > 10, which will allow the sort to use the index.
Why it's wrong here
A partial index with a filter on b > 10 would only include documents where b > 10. However, the query filters on a: 5 and b > 10, so the partial index could be used for filtering. But the sort on c still suffers from the same issue: b is a range predicate, so the index order after a is b, then c, and the sort on c cannot be supported because b varies. The partial index does not change the fundamental ordering problem. It would not allow the sort to use the index.
- ✗
Add a separate index on { c: 1 } and rely on the query planner to perform an index intersection with the existing index.
Why it's wrong here
MongoDB does not perform index intersection for sorting and filtering in the way this option suggests. While index intersection exists for some predicates, it is not used to combine a separate index on c for sorting with another index for filtering. The query planner would have to choose one index, and a separate index on c alone would not help with the filter on a and b. This approach is unlikely to improve performance.
- ✗
Modify the query to remove the range condition on b, so the existing index can support the sort on c.
Why it's wrong here
Removing the range condition changes the query semantics and is not a valid optimization if the range is required. The DBA should not alter the query to fit the index; instead, the index should be designed to support the query. While removing the range would allow the existing index to support the sort, it would return incorrect results. This is not a viable solution.
About these practice questions
Courseiva writes every C100DBA question from scratch — 222 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.