The covered queries quiz example is confusing and seems inaccurate

The detailed answer for one of the choices says this:

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

No, this query would use the { name: 1, dob: 1 } index, but it is projecting the hair field.

That doesn’t make a lot of sense for 2 reasons. 1. why would the query only use name: 1, dob: 1? I get the index of name dob is setup to take the the query more accurately. but hair and name would work also no? these are equality comparisons of find me bart or homer or are they considered ranged. 2. it is projecting the hair and the name field. That’s a fact. Event the other query which is correct uses dob 1, name 1 so how can that be different than the other one mentioned.

to me this either needs greater explanation or something is wrong with the question.

I recall i answered wrong on that quiz too :stuck_out_tongue:

  1. why would the query only use name: 1, dob: 1? I get the index of name dob is setup to take the the query more accurately. but hair and name would work also no?

If i remember correctly the index on hair is {hair:1, name:1} which means that first it will use hair field for indexing, then name. It cannot be used on a query predicate like { name : { $in : [ "Bart", "Homer" ] } } because, to be used, it must index the name field first^^

  1. it is projecting the hair and the name field. That’s a fact.

It’s true^^ But that’s exactly the reason it cannot be a covered query : the hair field is not present inside the { name: 1, dob: 1 } index, so to get the value of that field mongo must fetch the actual documents, the index itself is not enough ^^

These are my thoughts of course, hope they make sense and good luck for the course! :four_leaf_clover:

@xtianus

A covered query is one where the query can be entirely fulfilled using the index without going to retrieve any documents. Read the documentation here or review the Lecture on Covered Queries. Therefore as @Layer_58838 correctly points out, the query cannot be covered by {hair: 1, name: 1} since to use an index the query must access the fields in the same order as the index and the query does not ask for “hair”.

1 Like

ok this isn’t clicking exactly so let’s work this out. And I see 2 fold issues here. 1 is query predication so let’s clear that up first.

My understanding is that predication takes precedence but it’s not like indexing won’t occur via the next query predicate which in this case would have gone from hair_1_name_1… So indexing would have occurred no? @Layer_58838 does say to be use it must index the name field first… is that true? That is surely not something that was covered in the video.

Also, this is a direct passage from the documentation:

  • all the fields in the query are part of an index, and
  • all the fields returned in the results are in the same index.

This doesn’t seem as rigid as what @Layer_58838 is stating or what I am assuming to be the case. It must index the name first… really? Is this accurate.

Lastly, name is the only query. So anything with name in the projection will suffice. Why would this be different from one index to the other? The projection is purely to return said fields of the query and omit the id field. And, it matches what a query index is. Now the first portion of my thinking may be incorrect but this part can’t be. The wording in the answered description is not accurate in my opinion.

Again, this is from the documentation.

db.inventory.createIndex( { type: 1, item: 1 } )

This index will cover the following operation which queries on the  `type`  and  `item`  fields and returns only the  `item`  field:

db.inventory.find( { type: "food", item:/^c/ }, { item: 1, _id: 0 } )

@xtianus

To take your points in order. #1 – no, the query can use an index if and only if the query matches the elements of the index or is a proper sub-set of the index. So, in the case of the index { hair: 1, name: 1) this will match a query that is either find( {"hair": "black", "name": "David" } ) for example, or is find( { "hair": "brown" } ) but will not match a query that is find( {"name": "Jessica"} ).

The only potential point of confusion here is that the query optimizer can invert the order of the predicates in the query, so that find( {"name": "David", "hair"; "black" } ) is treated exactly as if it were find( {"hair": "black", "name": "David" } ) – which of course it is, logically. This is the example that you cite, where the query optimizer can and has rearranged the actual query find( { type: "food", item:/^c/ } ) into the format find( { item:/^c/, type: "food" } ) to match the index.

For point #2, since “hair” is not part of the query, it cannot be used as part of the index. However, the index { “name”: 1, “dob”: 1} can be used, since “name” is the first element of the query and therefore the query is a proper subset of the index. However, that index does not include “hair”. Therefore, when you project the “hair” field in the result, MongoDB has to retrieve the documents themselves to find the value for “hair” – since the index { “hair”:1, “name”: 1 } has not been and cannot be used for this query.

1 Like

Ok I get what you’re saying. I need to read over the predicate part and revisit that.

For 2nd part though… It is because hair isn’t apart of the query that is the reason why that portion doesn’t work. i.e. if we simply added hair at the first part of the query that would have made it an acceptable answer?

@xtianus

Exactly correct. If the query were find.( { “hair”: “black”, “name”: “David” } ) then the query would use the index { “hair”: 1, “name”, 1 } and the query would become a covered query.