3.34. MongoDB query analysis

3.34.1. Use explain() ¶

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

Next we create the gender and the gender in the users collection user_name Index of:

>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()

Above explain() The 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, which means that we are using the index.

• cursor Because this query uses indexes, and indexes in MongoDB are stored in the B-tree structure, this is also a cursor of type BtreeCursor. If an index is not used, the type of the cursor is BasicCursor. This key also gives the name of the index you are using, which allows you to view the system.indexes collection under the current database (automatically created by the system, which will be slightly mentioned due to the storage of index information) to get the details of the index.

• n The number of documents returned by the current query

• nscanned/nscannedObjects Indicates the total number of documents in the collection scanned by the current query, and our goal is to make this value as close as possible to the number of documents returned.

• millis The time required for the current query, milliseconds

• indexBounds The specific index used by the current query

3.34.2. Use hint() ¶

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

This approach can improve performance in some cases. An indexed collection and executes a multi-field query (some fields are already indexed).

The following query example specifies the use of gender and user_name Index field to query:

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

Can be used explain() Function to analyze the above query:

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