Index covered problem

Covered Queries
Problem:

Given the following indexes:

{ _id: 1 }
{ name: 1, dob: 1 }
{ hair: 1, name: 1 }

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.

I think this query would use the { hair: 1, name: 1 } index which can cover this query, right or not?

No it will not.

The query part does equality match on name:. So an index with name: as the first field will be used, as written

But it does not cover because the index does not include the field hair:.

3 Likes

{ name:1, hair:1 } would be a better index though and would cover.

{ hair: 1, name: 1 } would cover the query as well IF you add a filter on hair in the query.

2 Likes

indexes are made by the order of field names so your indexes will have name_dob and hair_name. (I forgot the exact naming, but the order remains)

since your query asks only for a name match, mongodb will look for an index starting with name... thus will discard hair_name index and use only name_dob. if no index starts with name..., then it will revert to full search without an index.

however, if you ask for a match with {name:"...", hair:"..."}, the match terms will be re-ordered as {hair:"...", name:"..."}to match the full hair_name index.

1 Like