MongoDB Covered Query & Analyzing Query With Example

In the last tutorial, we had seen learned about MongoDB Text Search. Now we will discuss MongoDB Covered Query and Analyzing Query with Examples.

A covered query is one which uses an index and does not have to examine any documents. In MongoDB analyzing query, we will cover $hint and $explain.

MongoDB Covered Query

The MongoDB covered query is one which uses an index and does not have to examine any documents. An index will cover a query if it satisfies the following conditions:

• All fields in a query are part of an index.
• All fields returned in the results are of the same index.

If we take an example of a collection named example which has 2 indexes type and item.

db.example.createIndex( { type: 1, item: 1 } )

This index will cover queries with operations on type and item fields and then return only item field.

db.example.find(
{ type: "game", item:/^c/ },
{ item: 1, _id: 0 }
)

Here, to cover the query, the document must explicitly specify _id: 0 to exclude the _id field from a result. However, this has changed in version 3.6. Over here an index can cover a query on fields within embedded documents.

Example – 1

Take an example of a collection named tutorials with documents of the following form:

{ _id: 1, user: { login: "student" } }

The collection is having the following index:

{ "user.login": 1 }

The above index will cover the following MongoDB covered query:

db.tutorial.find( { "user.login": "student" }, { "user.login": 1, _id: 0 } )

Example – 2

Now, we will take another example to understand it in a more better way.

Consider a document in examples collection.

{
"_id": ObjectId("53402597d852426020000002"),
"contact": "1234567809",
"dob": "01-01-1991",
"gender": "M",
"name": "ABC",
"user_name": "abcuser"
}

We will create a compound index for the examples collection on the fields gender and user_name.

>db.examples.ensureIndex({gender:1,user_name:1})

Now it will cover the following query:

>db.examples.find({gender:"M"},{user_name:1,_id:0})

From the above examples, we can say that MongoDB will not look into database documents but it will fetch the required data from indexed data which will make it very fast and efficient. An index cannot cover a query if:

• Any indexed field is an array.
• Any indexed field is a subdocument.

MongoDB Analyzing Query

This comes in the picture because it helps in measuring how effective the database and design is. We will look into $explain and $hint which are frequently used queries.

i. $explain

It provides information on the query, indexes used in the query and some statistics. It is good to use this if you want to know how well your indexes are optimized.

Now we will take an example to understand about this query. We will use the collection named examples.

{
"_id": ObjectId("53402597d852426020000002"),
"contact": "1234567809",
"dob": "01-01-1991",
"gender": "M",
"name": "ABC",
"user_name": "abcuser"
}
>db.examples.ensureIndex({gender:1,user_name:1})

Now we will use $explain query over it.

>db.examples.find({gender:"M"},{user_name:1,_id:0}).explain()

After executing the above line we will get the following output:

{
  "cursor" : "BtreeCursor gender_1_user_name_1",
  "isMultiKey" : false,
  "n" : 1,
  "nscannedObjects" : 0,
  "nscanned" : 1,
  "nscannedObjectsAllPlans" : 0,
  "nscannedAllPlans" : 1,
  "scanAndOrder" : false,
  "indexOnly" : true,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "indexBounds" : {
  "gender" : [
    [
      "M",
      "M"
    ]
  ],
  "user_name" : [
    [
      {
        "$minElement" : 1
      },
      {
        "$maxElement" : 1
       }
      ]
    ]
  }
}

Now we will understand the following output:

• The true value of indexOnly indicates that is has used indexing.
• Cursor field specifies the type of cursor used. BTreeCursor type indicates that we have used the index and also gives the name to it. BasicCursor tells us that a full scan was made without using any indexes.
• n indicates the number of documents matched.
• nscannedObjects tells the total number of documents scanned.
• nscanned tells us the total number of documents or index entries scanned.

ii. $hint

This operator forces the query optimizer to use the specified index to run a single query. This is beneficial when you want to test the performance of a query with different indexes. We will take the above example only with collection name as “examples”.

>db.examples.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1})

So, this was all about MongoDB Covered Query and Analyzing Query. Hope, you liked our explanation.

Summary

Hence, we have studied MongoDB covered query and analyzing query with their examples. In addition, we learned $explain and $hint operator with examples. Still, have a doubt? Feel free to ask in the comment section.