In this article, we are going to discuss about another very interesting MongoDB’s concept of analyzing queries. In the earlier article, we discussed the covered queries where the matching of the query condition is done by MongoDB based on the index without looking inside the actual fields of the documents as all the fields given in the covered query are the part of an index itself. Analyzing queries are used to measure the effectiveness of the database and indexing design. We will further discuss about the frequently used operators $explain and $hint for the analyzing queries in this article.
Using $explain operator in analyzing queries
The $explain operator is used to provide the information on the data retrieval query, indexes used in a query and other associated query statistics. It is recommended to use this operator during index analysis for their optimization. In the last article, we used the ‘covered_queries’ collection to store the 5 documents where each document has the fields as name, gender, occupation, city, and country as shown below.
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.
C:\Users\Aparajita>cd C:\Program Files\MongoDB\Server\3.2\bin
C:\Program Files\MongoDB\Server\3.2\bin>mongo.exe
MongoDB shell version: 3.2.10
connecting to: test
> use covered_queries
switched to db covered_queries
> db.covered_queries.save ( [
... { name: "Aparajita Jain" , gender : "F", occupation : "Employed" , city : "Toronto" , country : "Canada" },
... { name: "Anita Jain" , gender : "F" , occupation : "HomeMaker" , city : "Washington D.C." , country : "United States" } ,
... { name: "Abhilasha Jain" , gender : "F" , occupation : "Employed" , city : "New Delhi" , country : "India" },
... { name: "Bhaskar Jain" , gender : "M" , occupation : "Student" , city : "London" , country : "United Kingdom" },
... { name: "Mohit Jain" , gender : "M" , occupation : "Employed" , city : "London" , country : "United Kingdom" }] )
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 5,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
>
Next, we created the database index on the three fields as follows: name, gender, and occupation by using the ‘ensureIndex ()’ method as shown below.
> db.covered_queries.ensureIndex ({gender: 1,name: 1,occupation: 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
Now, we are going to analyze the following covered query by using the ’explain ()’ method as shown below.
> db.covered_queries.find({occupation:"Employed"},{name:1,gender:1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "covered_queries.covered_queries",
"indexFilterSet" : false,
"parsedQuery" : {
"occupation" : {
"$eq" : "Employed"
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"name" : 1,
"gender" : 1
},
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"occupation" : {
"$eq" : "Employed"
}
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "APARAJITA-PC",
"port" : 27017,
"version" : "3.2.10",
"gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
},
"ok" : 1
}
>
In the returned explanation, we can observe the following.
- The queryPlanner indicates the entire query plan for the data retrieval.
- It shows the parsedQuery condition on the occupation field.
- It is explaining the winning plan and the rejected plan for the data retrieval from the documents.
- Lastly, the associated server information where this query is fired in the MongoDB.
Using $hint operator in analyzing queries
The $hint operator is used to force the query optimizer to optimize the retrieval time and use the specified index to run a query. The $hint operator is often used when we want to test performance of a query with different database indexes. E.g., in the following query the index is specified on the gender, name and occupation fields to be used by the query as shown below.
> db.covered_queries.find({gender:"M"},{name:1,occupation:1,_id:0}).hint({gender:1,name:1,occupation:1})
{ "name" : "Bhaskar Jain", "occupation" : "Student" }
{ "name" : "Mohit Jain", "occupation" : "Employed" }
>
We can analyze the above query by using the $explain operator as shown below.
> db.covered_queries.find({gender:"M"},{name:1,occupation:1,_id:0}).hint({gender:1,name:1,occupation:1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "covered_queries.covered_queries",
"indexFilterSet" : false,
"parsedQuery" : {
"gender" : {
"$eq" : "M"
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"name" : 1,
"occupation" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"gender" : 1,
"name" : 1,
"occupation" : 1
},
"indexName" : "gender_1_name_1_occupation_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"gender" : [
"[\"M\", \"M\"]"
],
"name" : [
"[MinKey, MaxKey]"
],
"occupation" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "APARAJITA-PC",
"port" : 27017,
"version" : "3.2.10",
"gitVersion" : "79d9b3ab5ce20f51c272b4411202710a082d0317"
},
"ok" : 1
}
>
In the above retrieval result, we can observe the index used by the query for the data retrieval as “indexName” : “gender_1_name_1_occupation_1”. This explains the use of the $hint operator.
Conclusion: –
In this chapter, we discussed the concept of the analyzing queries and the use of the $explain and $hint operators to analyze and help in the data retrieval through the data retrieval queries in the MongoDB. These operators are mainly used during the performance enhancement of the retrieval queries.
