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.

• ✗

  { timestamp: 1, user_id: 1, status: 1 }

  Why it's wrong here

  This index places the sort field first, which prevents the index from being used effectively for equality filtering. The query engine would likely perform a collection scan or a suboptimal index scan, requiring an in-memory sort phase that consumes significant memory and limits scalability for large result sets.

• ✗

  { user_id: 1, timestamp: 1, status: 1 }

  Why it's wrong here

  Placing the sort field between two equality fields prevents the index from supporting a covered sort. The query engine will be unable to use the index to order the results, forcing a slow, blocking sort operation that could easily exceed the 32MB limit for memory-based sorting in MongoDB.

• ✓

  { user_id: 1, status: 1, timestamp: 1 }

  Why this is correct

  This structure follows the ESR rule perfectly. The equality fields appear first, narrowing down the search space, followed by the field required for sorting. This combination allows the query engine to retrieve documents in the desired order without performing an additional, resource-intensive sort operation after fetching the data.

• ✗

  { user_id: 1, status: 1 }

  Why it's wrong here

  While this index optimizes the filtering, it does not support the sort order. MongoDB will still need to perform an in-memory sort after retrieving the documents, leading to decreased performance. Including the sort field in the index is essential to eliminate the blocking sort and improve overall response latency.