Analyzing queries is a very important aspect of measuring how effective the database and indexing design is. We will learn about the frequently used $explain and $hint queries.
Using $explain
TheĀ $explainĀ operator provides information on the query, indexes used in a query, and other statistics. It is very useful when analyzing how well your indexes are optimized.
In the last chapter, we had already created an index for theĀ user’sĀ collection on fieldsĀ genderĀ andĀ user_nameĀ using the following query ā
>db.users.createIndex({gender:1,user_name:1})
{
"numIndexesBefore" : 2,
"numIndexesAfter" : 2,
"note" : "all indexes already exist",
"ok" : 1
}
We will now use $explain on the following query ā
>db.users.find({gender:"M"},{user_name:1,_id:0}).explain()
The above explain() query returns the following analyzed result ā
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mydb.users",
"indexFilterSet" : false,
"parsedQuery" : {
"gender" : {
"$eq" : "M"
}
},
"queryHash" : "B4037D3C",
"planCacheKey" : "DEAAE17C",
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"user_name" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"gender" : 1,
"user_name" : 1
},
"indexName" : "gender_1_user_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"gender" : [ ],
"user_name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"gender" : [
"[\"M\", \"M\"]"
],
"user_name" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "Krishna",
"port" : 27017,
"version" : "4.2.1",
"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"ok" : 1
}
We will now look at the fields in this result set ā
- The true value of indexOnly indicates that this query has used indexing.
- The cursor field specifies the type of cursor used. BTreeCursor type indicates that an index was used and also gives the name of the index used. BasicCursor indicates that a full scan was made without using any indexes.
- n indicates the number of documents matching returned.
- nscannedObjects indicates the total number of documents scanned.
- nscanned indicates the total number of documents or index entries scanned.
Using $hint
TheĀ $hintĀ operator forces the query optimizer to use the specified index to run a query. This is particularly useful when you want to test the performance of a query with different indexes. For example, the following query specifies the index on fieldsĀ genderĀ andĀ user_nameĀ to be used for this query ā
>db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1})
{ "user_name" : "tombenzamin" }
To analyze the above query using $explain ā
>db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1}).explain()
Which gives you the following result ā
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "mydb.users",
"indexFilterSet" : false,
"parsedQuery" : {
"gender" : {
"$eq" : "M"
}
},
"queryHash" : "B4037D3C",
"planCacheKey" : "DEAAE17C",
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"user_name" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"gender" : 1,
"user_name" : 1
},
"indexName" : "gender_1_user_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"gender" : [ ],
"user_name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"gender" : [
"[\"M\", \"M\"]"
],
"user_name" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "Krishna",
"port" : 27017,
"version" : "4.2.1",
109
"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"ok" : 1
}
