YouTip

# MongoDB Query Analysis MongoDB query analysis can help ensure that the indexes we have created are effective and is an important tool for analyzing query performance. Common functions used for MongoDB query analysis are: `explain()` and `hint()`. * * * ## Using `explain()` The `explain` operation provides query information, index usage, and query statistics, which is beneficial for optimizing indexes. Next, we create an index 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 result: { "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 in this result set: * **indexOnly**: This field is `true`, indicating that we used an index. * **cursor**: Since this query used an index, and MongoDB stores indexes in a B-tree structure, this also used a `BtreeCursor` type cursor. If no index was used, the cursor type would be `BasicCursor`. This key also provides the name of the index used. You can view the detailed information of the index by checking the `system.indexes` collection (automatically created by the system to store index information, which will be mentioned briefly later) in the current database using this name. * **n**: The number of documents returned by the current query. * **nscanned/nscannedObjects**: Indicates how many documents in the collection were scanned in this query. Our goal is to make this number 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 in the current query. * * * ## Using `hint()` Although the MongoDB query optimizer generally works well, you can also use `hint` to force MongoDB to use a specified index. This method can improve performance in certain situations. For a collection with an index and 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()