Pearson C100DBA New Attempt

For a compound index to satisfy a multi-field sort, the indexed fields must occur in the same sequence as the sort specification, and their directions must either match the requested pattern or be the complete reverse of it. The query sorts by { department: 1, salary: -1 }, so option B exactly matches both the key order and the requested directions. MongoDB can scan that index and return the results already ordered, avoiding a blocking in-memory sort. Option A uses { department: 1, salary: 1 }, which does not match the mixed-direction sort and is not its full inverse. Option D puts name before the two sort keys. Because the query has no equality predicate on name, that leading key prevents the later fields from directly providing the requested global sort. Option C is not a sensible index definition for this workload. Separately, the displayed projection mixes inclusion and exclusion (name:1, salary:1, department:0), which MongoDB normally disallows except for _id; that syntax issue should be corrected independently. It does not change the intended index answer for the sort, which is B.

Study Guide reference/topic:Indexing and Query Optimization - compound indexes, sort support, index prefixes, and key direction.

===========

When MongoDB ' s winning query plan uses a B-tree index, the plan includes an IXSCAN stage. That stage contains the index-specific information a DBA needs to identify how the query is being serviced, including the key pattern, index name, traversal direction, and index bounds. For example, keyPattern can show a compound definition such as { status: 1, createdAt: -1 }, making it straightforward to confirm which indexed keys participate in the chosen plan. A FETCH stage indicates that MongoDB is retrieving full documents, often after an index has produced candidate record identifiers; the fetch stage itself does not identify the index key. A COLLSCAN stage means MongoDB is scanning the collection and therefore is not using a normal index scan for the predicate. GROUP is unrelated to index selection. Effective DBA query analysis usually involves inspecting both queryPlanner.winningPlan and execution statistics such as totalKeysExamined, totalDocsExamined, and nReturned. But for the narrow question of which stage exposes the index key used by the winning plan, IXSCAN is the correct answer. Therefore option C is correct.

Study Guide reference/topic:Indexing and Query Optimization - explain output, winning plans, IXSCAN, key patterns, and index bounds.

===========