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

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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!

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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 }}