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.

• ✗

  { amount: 1, order_date: -1, status: 1 }

  Why it's wrong here

  This index places the Range field ('amount') before the Equality and Sort fields. This is inefficient because once a range is applied, the subsequent fields in the index cannot be used for sorting or further equality filtering without scanning many unnecessary keys, violating the ESR performance principles.

• ✗

  { order_date: -1, status: 1, amount: 1 }

  Why it's wrong here

  Placing the Sort field before the Equality field is suboptimal. If 'status' is not the first field, the database cannot jump to the 'urgent' documents and then read them in 'order_date' sequence. It would instead have to scan all 'order_date' entries and filter for 'status' manually.

• ✓

  { status: 1, order_date: -1, amount: 1 }

  Why this is correct

  This follows the ESR rule perfectly. 'status' is an equality match, 'order_date' is the sort field, and 'amount' is a range filter. By ordering them this way, MongoDB can filter by status, then use the index's natural order for the sort, and finally apply the range filter on the remaining entries.

• ✗

  { status: 1, amount: 1, order_date: -1 }

  Why it's wrong here

  In this configuration, the Range field 'amount' comes before the Sort field 'order_date'. When a range filter is encountered in an index scan, any subsequent fields cannot be used for sorting. This would force MongoDB to perform an expensive in-memory sort after filtering the documents by status and amount.