MongoDB - Query Analysis

user-pcr user-pcr
Feb 10, 2022 - 03:23
0 16
MongoDB - Query Analysis

Query analysis is a very important aspect of measuring the effectiveness of a database and indexing design. We will learn about commonly used queries $explain and $hint .

Using $explain

The $explain operator provides information about the query, the indexes used in the query, and other statistics. This is very useful when analyzing how well your indexes are optimized.

In the previous chapter, we have already created an index on a collection of users by gender, gender, and username using the following query:

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

Now, we will use $explain on the following query −

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

The above explain() query returns the following parsed 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"
         ]
      ],
      "username" : [
         [
            {
               "$minElement" : 1
            },
            {
               "$maxElement" : 1
            }
         ]
      ]
   }
}

We will now look at the fields in this result set −

  • A true value of indexOnly indicates that this query used indexing.

  • The cursor field specifies the type of cursor to use. The BTreeCursor type indicates that an index has been used and also gives the name of the index being used. BasicCursor indicates that a full scan was performed without using any indexes.

  • n indicates the number of returned documents.

  • nscannedObjects specifies the total number of scanned documents.

  • nscanned specifies the total number of scanned documents or index entries.

A true value of indexOnly indicates that this query used indexing.

The cursor field specifies the type of cursor to use. The BTreeCursor type indicates that an index has been used and also gives the name of the index being used. BasicCursor indicates that a full scan was performed without using any indexes.

n indicates the number of returned documents.

nscannedObjects specifies the total number of scanned documents.

nscanned specifies the total number of scanned documents or index entries.

Using $hint

The $hint operator causes the query optimizer to use the specified index to execute the query. This is especially useful when you want to test the performance of a query with different indexes. For example, the following query specifies an index for the gender and username fields to be used for this query −

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

To parse the above query with $ explain −