[MongoDB] : How Indexes works !

> use dbversitydb
switched to db dbversitydb
>
>
> for(i = 1; i <= 1000; i++) db.dbversity_website.insert( { post_id : i, comment_id : i, likes : i});
WriteResult({ “nInserted” : 1 })
>
>
>
> db.dbversity_website.findOne()
{
“_id” : ObjectId(“5527b4a29689e4b8db19b613”),
“post_id” : 1,
“comment_id” : 1,
“likes” : 1
}
>
>

> db.dbversity_website.find( { post_id : 250 } )
{ “_id” : ObjectId(“5527b4a29689e4b8db19b70c”), “post_id” : 250, “comment_id” : 250, “likes” : 250 }
>
> db.dbversity_website.find( { post_id : 250 } ).explain()
{
“cursor” : “BasicCursor”,
“isMultiKey” : false,
“n” : 1,
“nscannedObjects” : 1000,
“nscanned” : 1000,
“nscannedObjectsAllPlans” : 1000,
“nscannedAllPlans” : 1000,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 7,
“nChunkSkips” : 0,
“millis” : 0,
“server” : “vm-5cc5-bad6:27017”,
“filterSet” : false
}

Here MongoDB server is returning a basic cursor, as we do not have index on field post_id. Also note that, it’s scanning 1000 documents in the dbversity_website collection and scanning 1000 index keys in “system.indexes” collection, where the default key is “_id”.

> db.dbversity_website.ensureIndex( { post_id :1 , comment_id : 1 } )
{
“createdCollectionAutomatically” : false,
“numIndexesBefore” : 1,
“numIndexesAfter” : 2,
“ok” : 1
}
>
> db.system.indexes.find()
{ “v” : 1, “key” : { “_id” : 1 }, “name” : “_id_”, “ns” : “dbversitydb.dbversity_website” }
{ “v” : 1, “key” : { “post_id” : 1, “comment_id” : 1 }, “name” : “post_id_1_comment_id_1”, “ns” : “dbversitydb.dbversity_website” }
Here we’ve created index on key post_id and comment_id and it’s called compound key i.e., named here as post_id_1_comment_id_1.

Now explain of the above query shows that MongoDB server is returning a Btree cursor, meaning it’s making use of index key to execute the query/command. -see below.

Also not the number of objects scanned in the “dbversity_website” collection as well as in the ‘system.indexes” collection.

> db.dbversity_website.find( { post_id : 250 } ).explain()
{
“cursor” : “BtreeCursor post_id_1_comment_id_1”,
“isMultiKey” : false,
“n” : 1,
“nscannedObjects” : 1,
“nscanned” : 1,
“nscannedObjectsAllPlans” : 1,
“nscannedAllPlans” : 1,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
“post_id” : [
[
250,
250
]
],
“comment_id” : [
[
{
“$minElement” : 1
},
{
“$maxElement” : 1
}
]
]
},
“server” : “vm-5cc5-bad6:27017”,
“filterSet” : false
}

Covered Index or indexOnly :-
==========================
If our command is requesting for values present inside the index, mongo engine will fetch that information from the indexe collection itself and does not go to the collection where the entire document is present.
Hence the speed of execution of the command is more faster, and this type of queries are very efficient in creating optimized web applications.

Note from the below explain() result, the number of objects scanned in the collection is (nscannedObjects) 0, and the number of scans made on indexes in only (nscanned) 1.
And now you can also observe indexOnly is true.

> db.dbversity_website.find( { post_id : 250 } )
{ “_id” : ObjectId(“5527b4a29689e4b8db19b70c”), “post_id” : 250, “comment_id” : 250, “likes” : 250 }
>
> db.dbversity_website.find( { post_id : 250 }, { post_id : 1, comment_id : 1 , _id : 0} )
{ “post_id” : 250, “comment_id” : 250 }
>
>
> db.dbversity_website.find( { post_id : 250 }, { post_id : 1, comment_id : 1 , _id : 0} ).explain()
{
“cursor” : “BtreeCursor post_id_1_comment_id_1”,
“isMultiKey” : false,
“n” : 1,
“nscannedObjects” : 0,
“nscanned” : 1,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 1,
“scanAndOrder” : false,
“indexOnly” : true,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
“post_id” : [
[
250,
250
]
],
“comment_id” : [
[
{
“$minElement” : 1
},
{
“$maxElement” : 1
}
]
]
},
“server” : “vm-5cc5-bad6:27017”,
“filterSet” : false
}
>
>
>