Analytic Square

In this introductory article on Indexes in MongoDB we will learn how indexes works in MongoDB. Like in other databases in MongoDB also indexes play very important role. By using indexes we can optimize our query performance but on the other side it will hamper the performance if not used properly. Like a traditional example of indexes, in any book index help us to find the content/topic very easily. Same happen with MongoDB-Indexes help query engine by reducing time to fetch documents. In MongoDB we can create indexes by two types:
1. Single Key Index
2. Compound Key Index

1. Single Key Index: With this type of index each value of index corresponds to a single value from documents. Best example of this is default _id field in each document.
2. Compound Key Index: With this type of index we create index on combination of keys. That give us benefit over single key index when we search our documents on the basis of mixed condition.

To demonstrate this, let us create a collection by executing below java script on MongoDB shell:
for(i=0; i<500; i++) { db.DemoIndex.save({num: i}); }
To check the above syntax execute find():
db.DemoIndex.find()

If you want to see more results you can type “it” and press enter.

Lets try to find some more records:
db.DemoIndex.find({num:30})
Will return only one value: { “_id” : ObjectId(“5415f44f47919b61db429589″), “num” : 30 }

db.DemoIndex.find({num:{“$gt”:30,”$lt”:35}})
Will return 4 rows with num value: 31,32,33 and 34.

{ “_id” : ObjectId(“5415f44f47919b61db42958a”), “num” : 31 }
{ “_id” : ObjectId(“5415f44f47919b61db42958b”), “num” : 32 }
{ “_id” : ObjectId(“5415f44f47919b61db42958c”), “num” : 33 }
{ “_id” : ObjectId(“5415f44f47919b61db42958d”), “num” : 34 }

Now, at this point lets deep inside in Indexes and see how they works. In other relational databases we have some system commands/stored procedures which give us execution plan of query where we can see how our index is being used by query engine to find the result. Likely in MongoDB we have explain() command which gives us ides how index works. Lets try to modify the above query and check the use of explain():

db.DemoIndex.find({num:{“$gt”:30,”$lt”:35}}).explain()
Output of above query is as below:

{
“cursor” : “BasicCursor”,
“isMultiKey” : false,
“n” : 4,
“nscannedObjects” : 500,
“nscanned” : 500,
“nscannedObjectsAllPlans” : 500,
“nscannedAllPlans” : 500,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“server” : “Deepak-PC:27017″
}

The main things in that result are marked in Bold font.

Any one can be surprised to see that result, to search only 4 results (n) query engine scanned (nscanned) all 500 documents. The cursor type Basic Cursor means that this query has not used any index while executing.  In real scenario number of documents in collection much larger then we are using in our example. So, if that is the case then query engine will take lots of time to execute a very simple query.

Okay, we have a solution of this problem- We can make a index on our collection. ensureIndex() method is used to create an index on collection in MongoDB. In our example we have only one column- num. Below command will create an Index on num column:

db.DemoIndex.ensureIndex({num:1})
That  command ensure that an ascending index should be built on num column for all documents in DemoIndex collection.  Index has created or not? Below command will give us the answer:

db.DemoIndex.getIndexes()

Above command will show you all the indexes which have been created on the collection. We have the default index on _id and the one which we have just created on num. Now, again run below command and see the difference:
db.DemoIndex.find({num:{“$gt”:30,”$lt”:35}}).explain()

Now, it is very clear that query engine use to get the result by using the index created on num and scanned only 4 pages now. If we have more number of documents then we can see the significance difference in “millis” (query execution time in milliseconds) between the approaches we have followed.

In this article we have seen single key index only, in coming posts we will see how to deal with compound key index and dig some more into indexes in MongoDB.