C100DEV Indexing 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.

• ✗

  { category: 1 }

  Why it's wrong here

  Indexing only the category field allows the engine to locate documents efficiently, but it fails to support the sort operation. Without the timestamp field in the index, MongoDB must perform an in-memory blocking sort, which is inefficient for large datasets and can exceed memory limits.

• ✗

  { timestamp: -1, category: 1 }

  Why it's wrong here

  This index structure does not follow the ESR rule. Placing the sort field before the equality field forces the database to scan more index entries than necessary. It fails to provide the optimal prefix for narrowing the search space based on the category, leading to slower query execution times.

• ✓

  { category: 1, timestamp: -1 }

  Why this is correct

  Following the ESR rule, this index places the equality field first and the sort field second. This structure allows MongoDB to jump directly to the category and use the pre-sorted order of the index to return the timestamped results, avoiding costly in-memory sorting operations during query execution.

• ✗

  { timestamp: -1 }

  Why it's wrong here

  This index is completely ineffective for filtering by category. The database would need to perform a full collection scan or scan the entire index to find matching categories, resulting in extremely poor performance. The query optimizer would likely ignore this index entirely for the specified filtering requirements.