[MongoDB]: How do we create indexes

Let’s say you have a query which takes around 120 ms to execute without indexes i can assure you that with proper indexes you can reduce that time by at least 10 times in MongoDB.
Let me show you with an example.

First of all we need to create a big collection which contains few million records.
It’s easy to create such a collection from shell because it supports javascript using below for loop.

rs5:PRIMARY> use cate
switched to db cate
rs5:PRIMARY>
rs5:PRIMARY> db (OR)
cate
rs5:PRIMARY>
rs5:PRIMARY> db.getName()
cate
rs5:PRIMARY> for (val=0 val < 1000000 val++) { db.cate_col.save({value:val})}
rs5:PRIMARY>

The above command will take some time to execute in your shell because we are entering lot of records in a collection.
If you want to adjust values for number of records feel free to do so. Once the data has been entered into the collection we can query it normally.

rs5:PRIMARY> db.cate_col.count()
1000000
rs5:PRIMARY>0

Now we are sure that we have 1000000 records in our collection and that’s big enough for us to test indexes.

rs5:PRIMARY> db.cate_col.find({value:55550})
{ “_id” : ObjectId(“53dba5201c120d38dacde909”), “value” : 55550 }
rs5:PRIMARY>

Running a query on such a big collection brings some questions to my mind :

1. How much time was consumed in executing this query ?
2. How many records were scanned ?
3. Did this query use any index ?

MongoDB already provides with inbuilt functions which can answers all above questions in one go.
The answer is explain(). Let’s see an example :

rs5:PRIMARY> db.cate_col.find({value:555500}).explain()
{
“cursor” : “BasicCursor”,
“isMultiKey” : false,
“n” : 1,
“nscannedObjects” : 1000000,
“nscanned” : 1000000,
“nscannedObjectsAllPlans” : 1000000,
“nscannedAllPlans” : 1000000,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 373,
“indexBounds” : {

},
“server” : “hostname:37017”
}
rs5:PRIMARY>

Now our questions have been answered and we know that query took 373 ms to execute.
How about we create an index now and use that ? So this is how we create index in MongoDB

rs5:PRIMARY> db.cate_col.ensureIndex({value:1})
rs5:PRIMARY> // It will create an index on value field

But we need to verify if any index was created or not. Here we go

rs5:PRIMARY> db.cate_col.getIndexes()
[
{
“v” : 1,
“key” : {
“_id” : 1
},
“ns” : “cate.cate_col”,
“name” : “_id_”
},
{
“v” : 1,
“key” : {
“value” : 1
},
“ns” : “cate.cate_col”,
“name” : “value_1”
}
]
rs5:PRIMARY>

Now let’s run the same query again to find all values above 555500.

rs5:PRIMARY> db.cate_col.find({value:555500}).explain()
{
“cursor” : “BtreeCursor value_1”,
“isMultiKey” : false,
“n” : 1,
“nscannedObjects” : 1,
“nscanned” : 1,
“nscannedObjectsAllPlans” : 1,
“nscannedAllPlans” : 1,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
“value” : [
[
555500,
555500
]
]
},
“server” : “hostname:37017”
}

You must have noticed that following changes happened with the use of index.

Time without index : 373 ms
Records scanned without index : 1000000

Time with index : 0 ms
Records scanned with index : 0