Lab 2.1: Using Indexes to Sort - does lab answer match course info?

Hi. Can you please explain how this index:

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

is usable with this query?:

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

Lecture “Querying with Compound Indexes Part 1” at 5:25 clearly states that “order does matter”.

2 Likes

In lecture “When you can sort with Indexes” at 1:24 presenter swaps index keys and index does not work, because index prefix is not used when querying/sorting.

@Ramil_06268

Umm… yes, order does matter very much, but not in this specific instance. If we think about it and make ourselves a small example much like the example shown in the lecture, we see that the specific order here – “address.state”, “first_name” – will return exactly the same document as the query “first_name”, “address.state” since both of these are one specific value, right? So the MongoDB query analyzer is smart enough to switch the order to use the index.

Here’s an example using the two fields shown in the lecture (“last_name”, “first_name”). As you see, the query inverts these to use the index “first_name_1_last_name_1_address.city_1”.

MongoDB Enterprise > db.people.getIndexes()
[

{
“v” : 2,
“key” : {
“first_name” : 1,
“last_name” : 1,
“address.city” : 1
},
“name” : “first_name_1_last_name_1_address.city_1”,
“ns” : “m201.people”
},

]

MongoDB Enterprise > db.people.find( { “last_name”: “Pham”, “first_name”: “Yvonne” } ).explain()
{
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “m201.people”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“first_name” : {
“$eq” : “Yvonne”
}
},
{
“last_name” : {
“$eq” : “Pham”
}
}
]
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“first_name” : 1,
“last_name” : 1,
“address.city” : 1
},
“indexName” : “first_name_1_last_name_1_address.city_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“first_name” : ,
“last_name” : ,
“address.city” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“first_name” : [
“[“Yvonne”, “Yvonne”]”
],
“last_name” : [
“[“Pham”, “Pham”]”
],
“address.city” : [
“[MinKey, MaxKey]”
]
}
}
},
“rejectedPlans” :
},
“serverInfo” : {
“host” : “devserver03”,
“port” : 27017,
“version” : “3.6.8”,
“gitVersion” : “6bc9ed599c3fa164703346a22bad17e33fa913e4”
},
“ok” : 1
}

1 Like

I was watching lecture videos carefully, and there were no mentioning that switching fields can be detected by database, but opposite - all mentions of switching fields were commented as “this will not work”. If I’m wrong, please refer to the specific video.

Also, there was no mentioning of any “this specific instance”. What I see from lecture videos and “this specific lab” is missing information, which was not delivered in lectures.

Ramil_06268

While I understand your concerns, I think this isn’t being very productive at this point. Let me suggest that you do the following and then return to this.

  1. Finish all the lectures in the Chapter – particularly the next two – with the same attention and diligence you have applied to this one. These cover more issues on how indexes work and are built.

  2. Try the examples in the Lectures for yourself and see how they work.

  3. Then change various items in the examples and see for yourself how that affects the performance and results.

  4. Work through the Labs, especially Lab 2.2, and test those results.

Then, if you still have questions, post them here and I’ll try to help. Good luck.

1 Like

@Ramil_06268:
I was running into the same issues you were: originally my index wouldn’t work for any of the queries and after rebuilding it (i.e. destroy and re-create) it only worked for one.

Turns out: I make typos very easily :frowning:

adress.city != address.city

So yeah… If you’re not very, very careful when typing and you don’t use the picker in Compass when puttying the index together, then you’re not gonna have a good time.

The only way to be absolutely sure about your answers on these labs, is to try them out (paying extra attention to details).

EDIT:
Pffft :smile: How’s that for irony?! I suggest to you that you try out each and every case before answering, then I think I have the right answer on my last try so I don’t test and now I fail 2.1. #Irony

I got the same problem.

The video says clearly that “the order DOES matter”, when the lab’s answer says “the order DOES NOT matter”.

I was thinking the same about the ordering, but then again, DHz’s comments are logical too.

Now I’m wondering who will explain me what happens in this video: “Chapter 4: CRUD Optimization - Optimizing your CRUD Operations”

Something could be understood when you have index {a, b, c} and querying find({b,a}).sort({c}), but how will you explain find({a,c}).sort({b: -1}) with the same index? Where is index prefix now?

The prefix used is a.b. The c field is probably filtered in the fetch-phase. So:

  1. Index search on a
  2. Sort on b
  3. Filter on c in the fetch

What I see is that it uses a,c and then b in the query. Seems like every case is “special case” here.

The written query does seem like “a then c then b”, but I’ve seen it work out as “limit down to a, then sort by b, then filter out by c”. That’s something that’s not properly obvious from the training materials, but which is also explained in articles such as the following:

http://studio3t.com/knowledge-base/articles/mongo…

MongoDB will use the filter that provides the best cardinality for the initial query, even better if the filter will help in sorting. Thus the a.b. The final paring down based on C can be done in the fetch phase of the query, where it will simply disregard all other documents that don’t have C.

Yes, absolutely agree. And this is what I’m saying from day-1 - training material does not clearly explain how things work.

It could be that the ordering of the information provided in this course is sub-optimal. The first lesson in chapter 4 dives deeply and quickly into the exact issue you’re running into. It explains the need for “Equality, Sort, Range”.

I got the answer right on the third try (I really didn’t want to miss this one), after reading the following website:

          https://www.damiencosset.com/understanding-explain/

Just my opinion, I think it helped me so I recommend reading it.

2 Likes

I took this course twice due to failing on the first attempt and each time I answered wrong :frowning:

Hi @moein_84956,

I have initiated a discourse inbox thread with you. Please reply there with your doubt.

Kind Regards,
Sonali

The query optimizer looks for equality conditions and reorders the query to fit conditions with available indexes, so, the mongo’s query optimizer leaves swap conditions taking “first_name” as the first condition:

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

therefore, the index is used for IXSCAN on “first_name”

I can’t remember in which one, but certainly this is explained by Kirby on some video.

I believe they should create books for these classes. Students will purchase and use as reference. I like to read and things will fall into place and understand better. Just a thought. Thanks