Chapter 4 - Quiz 2 Confused by answer!

I’m working through the videos and quizes, but I don’t understand why the third option on the second quiz states that it would use the DOB index when it’s not mentioned anywhere in the query?

To me, the query

db.example.find( { name: { $in ["Bart", "Homer"] } }, { _id: 0, hair: 1, name: 1 })

Would be covered by the index

{ hair: 1, name: 1}

The detailed explanation states, it’s not covered because it would use the “dob” field which makes no sense as it’s never referenced anywhere, can anyone explain why it would use that index?

Edit:

So sitting thinking about it, I suddenly realised that it was using the DOB index, because the ordering of the fields on the index means that because the eqaulity search is on Name, it uses the

{ name: 1, dob: 1 }

As such because the project has the Hair field, the index didn’t cover and it would of resulted in a Fetch stage

I for one have been struggling with indexes and how one would build effective indexes for their queries. So I started reading up a little and found this useful article.

-> https://studio3t.com/knowledge-base/articles/mongodb-index-strategy/

In the example you mention, based on that article, we can assume that MongoDB used the name_1_dob_1 index for two reasons:

  1. The cardinality of “name” is better than that for “hair”.
  2. The “name” field is at the start of name_1_dob_1, as opposed to further into the index with hair_1_name_1.

Basically, using name_1_dob_1 allows MongoDB to cut down to a much smaller number of documents, much faster.

1 Like

Thanks @Tess_Sluijter, I realised just before you posted but thanks for the link and confirming my own thoughts!

1 Like