I don’t agree that the current solution is the best index. The argument is that it’s better than another for the 2nd query because of the sort, but it will perform a fetch on 8 times the amount of documents.
MongoDB seems to think the same: when both indexes are created, it will not choose the one in the solution.
Here are the stats, I renamed the indexes to avoid spelling out the answer:
We can see:
- a lot less index keys examined: 59 instead of 488
- a lot les documents examined: 8 instead of 67