Covered Queries in MongoDB

Covered queries help us in querying data faster. This is achieved by ensuring the index created contains all the fields required by the query. It doesn’t require examining any documents apart from the indexed ones.

We need to ensure that all the fields in the query, as well as results returned, are part of the index.

Please note that:

• Covered queries will not work on arrays and sub-documents.

• Cannot include _ID on results, e.g. _ID: 0, to be part of queried indexes.

Let’s walk through it with a simple example.

Step 1: Create a collection as follows:

db.products.insert( { "Name":"T-Shirt", "UnitPrice":345.00, "Qty":20 })

Step 2: Create a multi-key index:

db.products.createIndex({"Name":1,"Qty":1})

Step 3: Write a select query with explain(true): 

db.products.find({"Name":"T-Shirt","Qty":20}).explain(true)

Step 4: Watch the result of the previous step.

We inserted only one record and it states that the total docs examined is 1:

"executionStats" : {
"executionSuccess" : true,
 "nReturned" : 1
 "executionTimeMillis" : 0,
 "totalKeysExamined" : 1
 "totalDocsExamind" : 1,

Step 5: The covered query:

db.products.find({"Name":"T-Shirt","Qty":20},{"Name":"T-Shirt","Qty":20,_id:0}).explain(true)

Let me explain why I call the above a covered query.

You may notice that the only difference between Step 4 and Step 5 is _ID: 0.

Yes. That's precisely the point!

In the query, you can see that all the fields are part of the index created and we ensured _id was removed as part of results. That is because _id is the default index and its presence will blow the covered query rule.

The below results clearly indicate that it has not searched any document(s) at all. It just went through the index and found the result.

"executionStats" : {
"executionSuccess" : true,
 "nReturned" : 1
 "executionTimeMillis" : 0,
 "totalKeysExamined" : 1
 "totalDocsExamind" : 0,

I hope this article was useful!

If you enjoyed this article and want to learn more about MongoDB, check out this collection of tutorials and articles on all things MongoDB.