Analyzing queries is a totally critical thing of measuring how powerful the database and indexing design is. We will find out about the regularly used $explain and $trace queries.
Using $explain
The $provide an explanation for operator gives data at the query, indexes used in a query and other information. It may be very useful whilst reading how well your indexes are optimized.
In the final bankruptcy, we had already created an index for the users collection on fields gender and user_name using the subsequent query −
>db.users.createIndex({gender:1,user_name:1})
{
"numIndexesBefore" : 2,
"numIndexesAfter" : 2,
"note" : "all indexes already exist",
"ok" : 1
}
We will now use $provide an explanation for on the following query −
>db.users.find({gender:"M"},{user_name:1,_id:0}).explain()
The above provide an explanation for() question returns the following analyzed end 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 on this result set −
- The genuine fee of indexOnly suggests that this query has used indexing.
- The cursor subject specifies the form of cursor used. BTreeCursor kind shows that an index become used and also offers the name of the index used. BasicCursor suggests that a full scan was made without the use of any indexes.
- N indicates the wide variety of documents matching again.
- NscannedObjects suggests the overall quantity of files scanned.
- Nscanned suggests the overall quantity of files or index entries scanned.
Using $hint
The $trace operator forces the query optimizer to use the desired index to run a query. This is specially useful when you need to test overall performance of a question with distinctive indexes. For example, the subsequent query specifies the index on fields gender and user_name for use for this question −
>db.users.find({gender:"M"},{user_name:1,_id:0}).hint({gender:1,user_name:1})
{ "user_name" : "tombenzamin" }
To analyze the above question using $give an explanation for −
>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
}