Lab 2.2: Best index selection

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

Hi @Eliott_Coyac,

You bring a great point here!!

Let me check this with the team and get back to you. I hope the index my
_answer that you are referring to is either { "address.state": 1, "job": 1, "first_name": 1 } or { "job": 1, "address.state": 1, "first_name": 1 }

Please feel free to reach out if you have any questions.

Kind Regards,
Sonali

Thank you @Sonali_Mamgain

Yes, { "job": 1, "address.state": 1, "first_name": 1 }

I understand the “equality, sort, range” rule of thumb, but it’s only introduced in Chapter 4 (unless I missed it), and it depends on how wide the range are when making queries. Here the second query has a narrow range (1/26th of the alphabet).

Another thing, the my_answer index is arguably better for the first query. It fetches one more document, but a lot less keys are examined - only 3 - and it’s selected as well by MongoDB when both indexes are created.