Create a compound index following the ESR (Equality, Sort, Range) rule: category first for equality, price second for sorting, and lastSeen third for the range filter.
For this query pattern, the optimal index follows the ESR (Equality, Sort, Range) rule. Place the equality field (category) first to narrow down the result set. Then include the sort field (price) second so MongoDB can return results in sorted order without an in-memory sort. Finally, add the range field (lastSeen) last to filter within the sorted results. This ordering allows MongoDB to use the index efficiently for all three operations.
category first (Equality): MongoDB can quickly narrow down to only documents with matching category, significantly reducing the search space
price second (Sort): The index is already sorted by price within each category, so MongoDB can traverse in order without an expensive in-memory sort
lastSeen third (Range): Within the sorted price order, MongoDB can scan only the documents meeting the date range condition