M201 Lab 2.1 compound index used does not match course info

Hi everyone,

In the mongo documentation, as well as the lecture videos about compound indexes (especially the “Querying on compound indexes part 2”, and again on " When you can sort with Indexes" around 2:44) it has been stressed many times that the order of keys that comprise a compound index matters. This distinct, left-to-right, continuous, ordering of sub-sets of the index keys effectively forms what mongo calls an index prefix. Now, this fact by itself seems reasonable to me. What I, however, find very puzzling, and fail to understand is how, given the explanations provided in the teaching material (docs and videos) and the Lab’s 2.1 problem statement, can the following answer:

be considered correct. The problem statement asks for determining which queries are able to successfully use a given index for both filtering and sorting assuming the existence of the following index:

{ “first_name”: 1, “address.state”: -1, “address.city”: -1, “ssn”: 1 }

Now, valid prefixes for the above would presumably be:

{first_name },{first_name, address.state},
{ first_name, address.state, address.city}, { first_name, address.state, address.city, ssn}.
However, in the find predicate (according to the above and the course material instructions),
a valid prefix (before the sorting key) would be {first_name} (as {address.state, first_name} is not a valid prefix). The key in the sort predicate (address_city) does not form on its own a valid prefix either (again based on the requirements for something to constitute a valid prefix as phrased in the manual, or the videos). I could understand as being correct something of the following nature:
db.people.find({ “foo”: “bar”, “first_name”: “Jessica” }).sort({ “address.state”: -1 }), or even
db.people.find({ “first_name”: “Jessica”, “address.state”: “South Dakota”}).sort({ “address.city”: -1 })
I could even understand the fact that the given answer:
db.people.find({ “address.state”: “South Dakota”, “first_name”: “Jessica” }).sort({ “address.city”: -1 })
could be using an index scan for filtering (by using the first_name key) but not using an index for sorting. I do not get lab’s rational behind the provided answer, given that we want an index to be used for both filtering AND sorting at the same time. Can someone please clearly explain this (and please do not suggest the explain command output because to agree with what one sees, there has to be a solid understanding of why we are seeing what we are seeing, first).
Thanks

Hi @Evangelos_41527,

Please check your discourse inbox and let me know if you have any questions.

Thanks,
Sonali

I would also like to know this

@Sonali_Mangain, Thanks for your reply. In the document link that you provided i believe that you are referring to the middle row of the included table i.e.

db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } )

is that correct?
If so, i would kindly suggest that you somehow update the provided teaching material (either the video itself, or perhaps by including a note underneath the video as you so often do). I understand that as per the provided link the question is indeed covered, but since it is an exceptional case (as you mentioned yourself in your reply) and it is very easy to overlook it inside the pretty big manual that mongo has, and since (in my own opinion) the lectures provided DO NOT cover sufficiently that particular case (as you can see by my earlier comment), I feel it would only be to everyone’s best interest if people could straight away be mindful of that particular case. This would greatly enhance understanding of how indexes work AND what possible caveats in regards to their usage exist.
Thanks again.

@Jonathon_93506

Have a look at this link:
[https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/]

The middle row should be the one that is of interest in regards to the particulars of the discussion.
Cheers

1 Like

What’s important in this scenario is that the Query Optimiser will reorder the query predicates to match the optimal index. With this in mind, here are a few other takeaways:

  1. The order of the query predicates does not matter
  2. The order of the sort fields matter
  3. The order of the index keys within the compound index matter

Lastly none of the examples in the referenced doc are covered queries because the _id field was not explicitly excluded.

By the way @Evangelos_41527, please go ahead and redact remnants of the (partial) answers to this lab in an effort to keep in-line with the guidelines of this discussion board. Also, if you would like to continue the PM with Sonali, you’d need to find the initial notification from her, click on it and continue chatting in private:
image

Hi @Evangelos_41527,

Yes, if we are using Index: { a: 1, b: 1, c: 1 } , the above query will work because the query includes equality conditions on all prefix keys that precede the sort key.

We are planning to upgrade our course material and we will ensure that this feedback gets addressed.
Thanks for sharing this!!

Please let me know if you have any questions.

Thanks,
Sonali

Hi I have the exact same issue understanding the solution of this lab

Hi @madhuri_behl,

I hope the explanation provided in the posts above was useful.

If you have any specific questions, please let me know and we can have a detailed discussion.

Thanks.
Sonali

It is still not addressed in the video course and I fell into the same trap.