Query analysis in MongoDB

Analyzing queries is a very important aspect to assess the effectiveness of Database and the effectiveness of the designed index. We will explore the two most frequently used queries, $ explain and $ hint .

Use $ explain in MongoDB

The $ explain operator provides information about queries, indexes used in queries, and some other statistics. This operator is quite useful when analyzing how your indexes have been optimized.

In the previous chapter, we created an index for users collection on the gender and user_name fields with the following query:

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

Now, you use $ explain on the following query:

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

The above query will result in the analysis as follows:

 { 
 "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 
 } 
 ] 
 ] 
 } 
 } 

Look at the fields in this result set:

The true value of indexOnly indicates that this query has used indexing.

The cursor field determines the type of pointer used. The BTreeCursor type indicates that an index is used and also provides the name of the index used. BasicCursor indicates that a full scan has been performed without using any indexes.

Number n indicates the number of documents that have been matched in the result returned.

nscannedObjects shows the total number of scanned documents.

nscanned indicates the total number of documents or indexes that have been scanned.

Use $ hint in MongoDB

The $ hint operator Query Query to use the specified index to run a query. It is especially useful when you want to check the performance of a query with different indexes. For example, the following query identifies the index on the gender fields 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 }) 

To analyze the above query, use the operator $ explain in MongoDB:

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

According to Tutorialspoint

Previous post: Covered Query in MongoDB

Next lesson: Atomic Operation in MongoDB