MongoDB Query Analysis

MongoDB Query Analysis can ensure whether the indexes we have created are effective, and is an important tool for query statement performance analysis.

Common functions used in MongoDB Query Analysis include: explain() and hint().

Using explain()

The explain operation provides query information, index usage, and query statistics, etc. It is helpful for us to optimize indexes.

Next, we create indexes on gender and user_name in the users collection:

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

Now use explain in the query statement:

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

The above explain() query returns the following results:

{
   "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, let's look at the fields of this result set:

• indexOnly: The field is true, indicating that we have used an index.
• cursor: Because this query uses an index, and in MongoDB indexes are stored in a B-tree structure, it also uses a cursor of the BtreeCursor type. If an index is not used, the cursor type is BasicCursor. This key also gives the name of the index you are using. Through this name, you can view the system.indexes collection in the current database (automatically created by the system to store index information, which will be mentioned briefly later) to get detailed information about the index.
• n: The number of documents returned by the current query.
• nscanned/nscannedObjects: Indicates how many documents in the collection were scanned in total during this query. Our goal is to make this value as close as possible to the number of returned documents.
• millis: The time required for the current query, in milliseconds.
• indexBounds: The specific index used by the current query.

Using hint()

Although the MongoDB query optimizer generally works quite well, you can also use hint to force MongoDB to use a specified index.

This method can improve performance in certain situations. For example, an indexed collection and when executing a multi-field query (where some fields are already indexed).

The following query example specifies using the gender and user_name index fields for the query:

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

You can use the explain() function to analyze the above query:

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