Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Covered Queries in MongoDB

DZone's Guide to

Covered Queries in MongoDB

Walk through using covered queries in MongoDB in five steps so that all the fields in the query, as well as results returned, are part of the index.

Free Resource

Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.

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!

Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.

Topics:
mongodb ,indexing ,database performance ,covered queries ,querying ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}