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.

• ✗

  db.orders.createIndex({ customerId: 1, orderDate: 1 })

  Why it's wrong here

  This index has the correct field order (equality then sort), but the sort direction on `orderDate` is ascending, while the query sorts descending. MongoDB can use an index to support a descending sort by scanning the index in reverse, but only if the index is a simple index (single field) or if all sort directions are inverted. For a compound index, the sort direction must match exactly or be the exact reverse for all fields. Here, the query sorts by `orderDate: -1`, so an index with `orderDate: 1` would not support the sort efficiently; MongoDB would have to perform an in-memory sort.

• ✓

  db.orders.createIndex({ customerId: 1, orderDate: -1 })

  Why this is correct

  This index is correct because it follows the Equality, Sort, Range (ESR) rule. The query has an equality condition on `customerId` and a sort on `orderDate`. Placing the equality field first and the sort field second allows MongoDB to use the index for both filtering and sorting, avoiding an in-memory sort. The sort order in the index matches the query's sort order, which is crucial for performance.

• ✗

  db.orders.createIndex({ orderDate: -1, customerId: 1 })

  Why it's wrong here

  This index places the sort field before the equality field. While it could support the sort on `orderDate`, it would not efficiently filter by `customerId` because the index prefix is `orderDate`. The query would likely require scanning many index entries to find the matching `customerId`, leading to suboptimal performance. The ESR rule dictates that equality fields should come first.

• ✗

  db.orders.createIndex({ orderDate: 1, customerId: 1 })

  Why it's wrong here

  This index has both the wrong field order and the wrong sort direction. It places the sort field first and uses ascending order, while the query filters on `customerId` and sorts descending on `orderDate`. This index would not efficiently support the query's filter or sort, likely resulting in a collection scan or an inefficient index scan followed by an in-memory sort. It violates the ESR rule for both field order and sort direction.