mongoDB : Get information about query plan

Query plan specifies the plan that server uses to find matches for a query. You can get query plan using "cursor.explain()" method. By using explain() result, you can find exactly what index is used in querying.

> db.employee.find().explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 5,
        "nscannedObjects" : 5,
        "nscanned" : 5,
        "nscannedObjectsAllPlans" : 5,
        "nscannedAllPlans" : 5,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 1,
        "server" : "RENT-MIS-LT3016:27017",
        "filterSet" : false
}
>

"cursor" : "BasicCursor"

“BasicCursor” means, there was no index used, to execute this query.

"millis" : 1

Number of milliseconds used to execute this query.

let’s create index on firstName and lastName fileds of employee and query.

> db.employee.ensureIndex({firstName:1, lastName:1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

> db.employee.find({"firstName" : "Hari Krishna"}).explain()
{
        "cursor" : "BtreeCursor firstName_1_lastName_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "firstName" : [
                        [
                                "Hari Krishna",
                                "Hari Krishna"
                        ]
                ],
                "lastName" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "server" : "RENT-MIS-LT3016:27017",
        "filterSet" : false
}
>

This time explain(), output much longer than previous one.

"cursor" : "BtreeCursor firstName_1_lastName_1"

It uses index that I created on firstName and lastName. "BtreeCursor firstName_1_lastName_1" is the name of the compound index on firstName and lastName. It no longer used BasicCursor.

"isMultiKey" : false

Specifies whether index is a multi key index or not. It is not a multi key index, so it set to false.

"n" : 1

The number of documents returned from query.

nscannedObjects" : 1

nscannedObjects is the total number of documents scanned.

"nscanned" : 1

nscanned is the total number of index entries scanned

"nscannedObjectsAllPlans" : 1

nscannedObjectsAllPlans is a number that reflects the total number of documents scanned for all query plans during the database operation.

"nscannedAllPlans" : 1

nscannedAllPlans is a number that reflects the total number of documents or index entries scanned for all query plans during the database operation.

"scanAndOrder" : false

scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results.

"nYields" : 0

nYields is a number that reflects the number of times this query yielded the read lock to allow waiting writes to execute.

"nChunkSkips" : 0

nChunkSkips is a number that reflects the number of documents skipped because of active chunk migrations in a sharded system. Typically this will be zero. A number greater than zero is ok, but indicates a little bit of inefficiency.

indexBounds

indexBounds is a document that contains the lower and upper index key bounds.

For more information, go through below link.

http://docs.mongodb.org/manual/reference/method/cursor.explain/

Prevoius                                                 Next                                                 Home