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.

• ✗

  { metadata: 1 }

  Why it's wrong here

  Indexing the embedded document metadata as a whole is rarely useful for these queries, as they do not filter on metadata. Such an index would not support the common query patterns and would add unnecessary overhead.

• ✓

  { user_id: 1, timestamp: -1 }

  Why this is correct

  This index supports query (1) by providing an equality match on user_id and a sort on timestamp descending. The index prefix is user_id, and the sort field follows, so the index can be used efficiently for both filtering and sorting, avoiding an in-memory sort.

• ✗

  { user_id: 1, action: 1, timestamp: -1 }

  Why it's wrong here

  This compound index could support query (1) if action is not used, but the index prefix user_id is equality and then action is not in the query, so the sort on timestamp would not be supported because action is missing. For query (2), it is not optimal because action is not the leading field. Thus it is less effective than the two chosen indexes.

• ✗

  { timestamp: 1 }

  Why it's wrong here

  A single-field index on timestamp alone can help with time-range queries but does not include action, so query (2) would still need to filter action after scanning the index. It also does not help query (1) because user_id is not indexed, leading to inefficient filtering.

• ✓

  { action: 1, timestamp: 1 }

  Why this is correct

  This index supports query (2) by allowing an equality match on action and a range on timestamp. The index order (action then timestamp) enables efficient filtering and can also support sorting if needed. It is optimal for queries that filter on action and a timestamp range.