Lab 2.1 4th option is not correct answer

Hi,
I believe, this should does not use indexes for filtering & sort.

db.people.find({ “address.state”: “South Dakota”, “first_name”: “Jessica” }).sort({ “address.city”: -1 })

Reason:
Filtering prefix order is not same as in index.
Index order:
{ “first_name”: 1, “address.state”: -1, “address.city”: -1, “ssn”: 1 }
Filtering order:
db.people.find({ “address.state”: “South Dakota”, “first_name”: “Jessica” })
So filtering should be using CollScan rather than IXScan

Same thing with sort as well.
address.city is not in prefix order at all.

Please let me know if I am not correct in understanding.

@Harishalwala

You are not correct. The query optimizer will rearrange the order in the query to match the index if possible – which it is in this case, as both “address.state” and “first_name” are equality tests. Review Chapter 4 on CRUD optimization.

Not convinced yet.

From the videos, as I understood, Filtering & Sorting depends on prefix order of the index created. Now as you mentioned, if MongoDB has ability to exchange order, is the question not ambiguous??
However I will read through Chapter 4 and will then come back.

Thanks

@Harishalwala I was also stumped by this one, but when you run the explain test in compass, it does pass. I think the videos assumed we knew what the equality test in the filtering stage was, (I’m assuming this convenience feature to rearrange the predicates as long as they are present is a part of it), but the videos were adamant about preserving the order. The sorting part makes sense, if the other two answers are correct, since sorting is done on an already indexed collection, and the predicate matches the prefix field.