Covered Queries – MongoDB

By | April 28, 2018

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

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

Please note that

1.     Covered queries will not work on Arrays and Sub Documents.

2.     Cannot include _ID on results. i.e. _ID: 0 to be part of queried indexes

 Let’s walk through with a simple example:

Step 1: Creating 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 out the result of previous step

We inserted only one record. It states total keys examined is 1 [ All docs examined ]

 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 say the above one is covered query.

You may wonder the only difference between Step 4 and Step 5 is _ID:0

Yes. Precisely that’s point.

In the query you can see all the fields in the query are part of the index created and _id removed as part of results. _id is default index and it’s presence will blow the covered query rule.

Below results clearly indicates it has not searched any document(s) at all. Just went through the index and found the result.

Hope it was useful.

Leave a Reply

Your email address will not be published. Required fields are marked *