Chapter 4 - Covered queries

Can someone explain for the quiz why the answer is the following:

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

Based on the following indexes that are created:

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

Shouldn’t the answer be the following:

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

Based on the definition of a covered query (all fields in the query are a part of the index and all the fields are turned in the result are in the same index.)

In order to clarify, the first question is, which index did it use?

Hi @steperez,

This will be the correct answer because name field in the query filter and both dob and name field in projection will use { name:1, dob: 1} index.

This query will again use { name:1, dob: 1} index as it is the only index covering name field first. This query will be thus the wrong choice as it will not be a covered query.

Please let me know if you have any questions.

Thanks,
Sonali

4 Likes

I too have the doubt . can you explain little more, In the video explanation , for a covered query , index values should be present in the projection values . As you say do we need to consider the position of index values too?

Hi @Lifas_Kassim,

I would recommend you to go through the following documentation link:

Please feel free to reach out if you have any additional questions.

Kind Regards,
Sonali

The documentation could be clearer. It says:

"- all the fields in the query are part of an index, and

  • all the fields returned are in the same index…"

Maybe the last line should say: “all the fields returned are in that same index”. How it is currently written suggests that two indexes could be used.

Hey @Keith_O_Dulaigh,

You can share any feedback related to the MongoDB documentation on the same page itself.

In the documentation, you will see the “Was this page helpful?” option:

Click on No and then you can share your feedback in the pop-up as below:
Screenshot 2021-01-07 at 5.47.31 PM

Please feel free to reach out if you have any questions.

Kind Regards,
Sonali

…maybe add a reminder about index prefix. I saw hair/name in the projection and name in the index, but forgot name in the query will use the index name/dob (prefix).

The only explanation which I understood is the “query predicate sequence = sequence of Index = Projection items”
So, name is in query predicate, it will choose the index {name:1, dob:1} and hence the projection will be with dob and name

so, Query 1:
db.example.find( { name : { $in : [ “Bart”, “Homer” ] } }, {_id : 0, hair : 1, name : 1} )
Query (start with name) ----------> chosen index {name:1,dob:1} but projection says {hair:1, name:1} so this is wrong

Query 2:
db.example.find( { name : { $in : [ “Bart”, “Homer” ] } }, {_id : 0, dob : 1, name : 1} )
Query (start with name) ----------> chosen index {name:1,dob:1} and projection says {dob:1, name:1}, so this is ok.

So, in all, item in Index should match the Projection.

1 Like

Projection is a subset of index.

1 Like

@Sonali_Mamgain I have a follow-up question. It makes sense that the index that best satisfies the query is selected rather one that would be covered. However, if I using hint choose the { hair: 1, name: 1 } index, shouldn’t this be covered? I would expect to inspect more indexes than documents returned, since I query by name when hair is the first parameter in the index, but this still does a FETCH and documents need to be examined. (Also, what is the PROJECTION_SIMPLE-stage?)

The commands I issued:

db.examples.insert({name: "Alfred", dob: "kek", hair: "blue"})
db.examples.insert({name: "Lola", dob: "kek", hair: "blue"})
db.examples.createIndex({ name: 1, dob: 1})
db.examples.createIndex({ hair: 1, name: 1 })
let exp = db.examples.explain("executionStats")
exp.find({ name: "Alfred"}, {_id: 0, hair: 1, name: 1}).hint({hair: 1, name: 1})

The explain output:

"executionStats" : {
	"executionSuccess" : true,
	"nReturned" : 1,
	"executionTimeMillis" : 0,
	"totalKeysExamined" : 2,
	"totalDocsExamined" : 2,
	"executionStages" : {
		"stage" : "PROJECTION_SIMPLE",
		"nReturned" : 1,
		"executionTimeMillisEstimate" : 0,
		"works" : 3,
		"advanced" : 1,
		"needTime" : 1,
		"needYield" : 0,
		"saveState" : 0,
		"restoreState" : 0,
		"isEOF" : 1,
		"transformBy" : {
			"_id" : 0,
			"hair" : 1,
			"name" : 1
		},
		"inputStage" : {
			"stage" : "FETCH",
			"filter" : {
				"name" : {
					"$eq" : "Alfred"
				}
			},
			"nReturned" : 1,
			"executionTimeMillisEstimate" : 0,
			"works" : 3,
			"advanced" : 1,
			"needTime" : 1,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"docsExamined" : 2,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 2,
				"executionTimeMillisEstimate" : 0,
				"works" : 3,
				"advanced" : 2,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"keyPattern" : {
					"hair" : 1,
					"name" : 1
				},
				"indexName" : "hair_1_name_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"hair" : [ ],
					"name" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"hair" : [
						"[MinKey, MaxKey]"
					],
					"name" : [
						"[MinKey, MaxKey]"
					]
				},
				"keysExamined" : 2,
				"seeks" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0
			}
		}
	}
}

It looks like you only have 1 document. Why do you expect more indexes inspected? You only have 1 entry in your index and you are looking for an exact match.

When unsure, always refer to documentation. See

As for PROJECTION_SIMPLE, I am not too sure if it is used anymore.

That wasn’t the point of the example, but I realize it might have been confusing. I edited my post. I meant that since the index is { hair: 1, name: 1 } but I query by name only, I would have to inspect every entry in that index when looking for a certain name. But I still wouldn’t expect to have to inspect documents or do a fetch since all info that I’m after exists in the index.

In short, why isn’t the query a covered query?