Dedicated to design and performance of databases and audio systems.
MongoDB provides a query explain capability that can be used to diagnose performance issues, or simply provide insight into the metrics relative to the query.
In creating a simple 1,000,000 document collection via
> for ( i = 0; i < 1000000; i++ ) {
db.users.insert( {
"i" : i,
"username" : "user" + i,
"age" : Math.floor(Math.random() * 100),
"created" : new Date()
} );
}
and, executing the following query's explain()
> db.users.find( { username : "user101" } ).explain()
produces the result of
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1000000,
"nscanned" : 1000000,
"nscannedObjectsAllPlans" : 1000000,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 7812,
"nChunkSkips" : 0,
"millis" : 627,
"server" : "127.0.0.1:27017",
"filterSet" : false
}
From this we can deduce
"cursor" : "BasicCursor"
"n" : 1
"millis" : 627
"nscanned" : 1000000
Adding an index to the collection by the way of
> db.users.ensureIndex( { "username" : 1 } )
coupled with the original query
> db.users.find( { username : "user101" } ).explain()
now produces an explain()
result of
{
"cursor" : "BtreeCursor username_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexbounds" : {
"username" : [
[
"user101",
"user101"
]
]
},
"server" : "127.0.0.1:27017",
"filterSet" : false
}
Now we see
"cursor" : "BtreeCursor username_1"
"isMultiKey" : false
"n" : 1
"nscannedObjects" : 1
"nscanned" : 1
"nscannedObjectsAllPlans" : 1
"scanAndOrder" : false
"indexOnly" : false
"nYields" : 0
"millis" : 0
"indexbounds" : {
"username"[
[
"user101",
"user101"
]
]
}
Some of these attributes take on slightly different meanings depending on the query's use of an index or not.
nscannedObjects
→ with an index it is the number of times it follows a pointer; otherwise it is most likely a full collection scan.nscanned
→ the number of index entries looked at or number of documents scanned if an index was not used.
I hope this helps shed a little light on the MongoDB explain()
capability.
Cheers
-Brad