C100DBA Indexing and Performance Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  { score: -1, active: 1 }

  Why it's wrong here

  This index leads with score in descending order, which matches the sort, but the equality filter on active is placed after score. MongoDB can use the index to provide the sort order, but it cannot efficiently filter on active because active is not a prefix of the index. It would have to scan all score values and filter active as a residual predicate, which is less efficient than placing the equality field first. The query planner may still choose a collection scan if the filter is selective.

• ✗

  { score: 1, active: 1 }

  Why it's wrong here

  This index leads with score ascending and places active second. It does not efficiently support the equality filter on active because active is not a prefix. It also provides score in ascending order, while the query sorts descending. MongoDB could reverse scan the index for the sort, but the filter on active would still be a residual predicate applied after scanning many score values. This index is not optimal for the query pattern.

• ✗

  { active: 1, score: 1 }

  Why it's wrong here

  This index has active first, which is good for the equality filter, but score is ascending. The query sorts by score descending. MongoDB can traverse an ascending index in reverse to satisfy a descending sort, so this index could potentially support the sort. However, the question asks for an index that allows both filter and sort; the ascending index on score would require a reverse scan, which is possible but may not be as optimal as a directly matching descending index. In practice, MongoDB can use either, but the descending index is a more direct match.

• ✓

  { active: 1, score: -1 }

  Why this is correct

  The query has an equality filter on active and sorts by score descending. An index with active first (equality) and score second in descending order allows MongoDB to filter on active and then scan the index in score descending order. This satisfies both the filter and the sort without an in-memory sort. The leading equality field ensures the index can be used efficiently for the filter, and the sort field follows in the correct direction.