Click here to Skip to main content
15,867,756 members
Articles / Database Development

MongoDB Tutorial - Day 3 (Performance - Indexing)

Rate me:
Please Sign up or sign in to vote.
4.95/5 (25 votes)
18 Apr 2016CPOL8 min read 25.7K   20   4
Performance - Indexing in MongoDB
In this part of the MongoDB tutorial, we will see different aspects of Performances

Introduction

Welcome to Day 3 of MongoDB Tutorial. This is the third part of MongoDB Tutorial Series. In this part, we will see different aspects of Performances. Performance is always an important part of any database. In any database, whether a RDBMS or No-SQL database, we always think of how we can increase the query response time because the performance of your database is always a vital part of the overall performance of your application. Whenever we talk about the performance, Indexes comes on Priority. In this article, we will cover different types of indexes in MongoDB.

Background

Before reading this article, it will be good to cover my previous two parts of this article (Day 1 and Day 2).

  1. Mongo DB Tutorial and Mapping of SQL and Mongo DB Query
  2. MongoDB Tutorial - Day 2

What we covered so far is:

  1. Introduction of No-Sql (Different types of database comes under No-SQL)
  2. How to install and setup MongoDB on your machine.
  3. Introduction of Robomongo (MongoDB open source management tool)
  4. MongoDb Terminology
  5. How to insert a document in MongoDB
  6. How to select a document in MongoDB
  7. Where clause, Greater than and less than
  8. Like, logical AND and logical OR
  9. in operator in MongoDb, Count and Sort records in MongoDB
  10. Update, Delete, Remove and Drop in MongoDB
  11. Top, Distinct and Backup in MongoDB
  12. Schema less Behavior
  13. $exists, $in, $all, $nin
  14. Data Types in MongoDB
  15. Embedded documents and Dot Notation

What I Will Cover

  1. Indexes in MongoDB
  2. How to Create an Index in MongoDB
  3. Different Types of Index in MongoDB
  4. Index Properties
  5. Limitations

Indexes

We can think of Indexes like a Book Index. Suppose we are searching a topic in a book and we don't have indexes,  then we need to scan every page until we will reach that page. If your book contains 100 pages, then you can manage (if you have enough free time), but suppose your book contains 1 million pages, then it will be a very tedious job to search the topic by flip through every page. We have the same concept in MongoDB.

If we don't have indexes, then mongodb will do a complete collection scan to select the document that matches the query statement. If document count is high in collection, than it will be a death of performance.

Suppose we have the below documents in Names Collection as below:

C#
db.Names.insert({"Name":"Ajay"})
db.Names.insert({"Name":"Manoj"})
db.Names.insert({"Name":"Preeti"})
db.Names.insert({"Name":"Anuj"})
db.Names.insert({"Name":"Tony"})
db.Names.insert({"Name":"Steve"})
db.Names.insert({"Name":"Smith"})
db.Names.insert({"Name":"David"})
db.Names.insert({"Name":"William"})

Image 1

Documents in Names collection will store in an arbitrary order. If there is no Index and if we will find a document like below, then it will be complete collection scan and it will down the performance:

C#
db.Names.find({"Name":"Smith"})

If we want to find out what MongoDB is doing with the above Query, just use explain() like below:

Image 2

Here are some points to notice:

  • cursor : BasicCursor: It means MongoDB will perform a complete collection scan.
  • nscannedObjects: MongoDB scans nine objects to match this query.

So now the question arises:

What is Index: Index is an order set of items. Index stores the values in a specific order.

Default Index

When we create a collection in MongoDB, then MongoDB creates a unique Index on _id Field automatically. Because it's a unique index, it prevents us to enter duplicate values in _id field. We cannot drop this index in MongoDB.

How to Create an Index

To create an index in MongoDB, we have two methods:

  1. createIndex(): Syntax of createIndex() method is:
    C#
    db.CollectionName.createIndex({"Key":1 or -1})

    1 is for ascending and -1 is for descending order. So if we want to create a Index on Name key in Names collection, then we will create an index as below:

    C#
    db.Names.createIndex({"Name":1})
  2. ensureIndex(): Syntax of ensureIndex() method is:
    C#
    db.CollectionName.ensureIndex({"key": 1 or -1})

    ensureIndex() method is deprecated since version 3.0.0. This method is an alias of createIndex().

Different Types of Indexes in MongoDB

1. Single Field Index

Apart from the default Index on _id field created by MongoDB, users can create ascending or descending index on a single field.

We create a single key index as below:

C#
db.Names.createIndex({"Name":1})

Now, just run the explain():

Image 3

Ohh wow! Now we have an index (BtreeCursor Name_1) defined instead of Basic Cursor and most importantly, look at the nscannedObjects which is 1 now it means MongoDB scanned only 1 document which we mentioned in the query.

Suppose we have another collection named users which have the below documents:

C#
db.Users.insert({"Name":"Ajay","Age":30})
db.Users.insert({"Name":"Manoj","Age":60})
db.Users.insert({"Name":"Preeti","Age":20})
db.Users.insert({"Name":"Anuj","Age":70})
db.Users.insert({"Name":"Tony","Age":25})
db.Users.insert({"Name":"Steve","Age":18})
db.Users.insert({"Name":"Smith","Age":33})
db.Users.insert({"Name":"David","Age":53})
db.Users.insert({"Name":"William","Age":65})

Now suppose we want to find out all the documents where Age is greater than 30 and less than 60.

Image 4

So we have BasicCursor, so it will be a complete table scan and total documents scanned by the query is 9. Now I am defining a index on Age:

C#
db.Users.createIndex({"Age":1})

Now again, run the query which will find out all the documents where Age is greater than 30 and less than 60.

Image 5

After Index, MongoDB will not do a complete table scan, it will only scan 4 rows.

Image 6

2. Compound Index

Sometimes, we want to search based on both Name and Age. In that case, we will have to apply index on Name and Age both and this will be called Compound Index.

Syntax: db.CollectionName({"Key1":1 or -1,"Key2": 1 or -1,"KeyN":1 or -1})

We will create an Index on Name and Age both in Users collection as below:

C#
db.Users.createIndex({"Name":1,"Age":-1})

Note: Compound Index will work only if we will search on Name or Name and Age. If we will search through Age only, then Compound Index will not work.

Suppose we are searching through Name, then we can see that Compound Index is in use.

Image 7

If we will search through Name and Age, then we can see again Compound Index is in use.

Image 8

But if search only on Age, then we can see that Compound Index is not in use:

Image 9

So no Index is using in that case.

3. Multikey Index

Remove the existing documents from Users collection and insert some documents with interest in Users collection as below:

C#
db.Users.remove({})
db.Users.insert({"Name":"Ajay","Age":30,Interest : ["cricket","music"] })
db.Users.insert({"Name":"Manoj","Age":60,Interest : ["cricket","driving"]})
db.Users.insert({"Name":"Preeti","Age":20,Interest : ["music","driving"]})
db.Users.insert({"Name":"Anuj","Age":70,Interest : ["cooking","music"]})
db.Users.insert({"Name":"Tony","Age":25,Interest : ["swimming","cooking"]})
db.Users.insert({"Name":"Steve","Age":18,Interest : ["dancing","music"]})
db.Users.insert({"Name":"Smith","Age":33,Interest : ["tennis","tv"]})
db.Users.insert({"Name":"David","Age":53,Interest : ["music","swimming"]})
db.Users.insert({"Name":"William","Age":65,Interest : ["dancing","swimming"]})

Now if we want to Index the content of an array (Interest in my case), then we will use the Multikey Index.

Syntax: db.CollectionName.createIndex({"Array": 1 or -1})

We will create a MultiKey Index in Interest as below:

C#
db.Users.createIndex({Interest : 1})

4. Text Index

If we are performing a text search, then for better performance, we can apply the Text Index on string content.

We can create a text index only on string fields.

Syntax: db.CollectionName.createIndex({Field Name:"text"})

Suppose we want to create an Index on Name field, then we will create a text index as below:

C#
db.Users.createIndex({Name : "text"})

Note: A collection can have at most one text index.

Apart from these Indexes, MongoDB supports some more indexes which includes Geospatial and Hashed Index.

Geospatial Index is for better queries on geospatial coordinate data and Hashed Index Indexes the hash of the value of a field.

MongoDB Index Properties

1. Unique

unique property on an index field allows MongoDB to not accept a duplicate value for index field. In other words, unique property will restrict insert duplicate value for Index field.

I am adding an another column named "SSN" in Users collection and adding a index on "SSN" Field with Unique Property as below:

C#
db.Users.drop()
db.Users.createIndex({SSN:1},{unique:true})
db.Users.insert({"Name":"Ajay","Age":30,
Interest : ["cricket","music"] ,"SSN" : "12345"})
db.Users.insert({"Name":"Manoj","Age":60,
Interest : ["cricket","driving"],"SSN" : "54321"})

I removed all the records from Users collection and create a index on SSN Filed with unique property. So if I will try to insert duplicate values in SSN, then I will get the error. Let's try it:

C#
db.Users.insert({"Name":"Preeti","Age":20,
Interest : ["music","driving"],"SSN" : "54321"})

and the error says:

C#
insertDocument :: caused by :: 11000 E11000 duplicate key error index: 
                 Test.Users.$SSN_1  dup key: { : "54321" }

So we can't insert duplicate values in a field on which we have an index with unique property.

2. Sparse

I am dropping the Users collection and will insert some document as below:

C#
db.Users.drop()
db.Users.insert({"Name":"Ajay","Age":30,
Interest : ["cricket","music"] ,"SSN" : "12345"})
db.Users.insert({"Name":"Manoj","Age":60,
Interest : ["cricket","driving"],"SSN" : "54321"})
db.Users.insert({"Name":"Preeti","Age":20,
Interest : ["music","driving"]})
db.Users.insert({"Name":"Anuj","Age":70,
Interest : ["cooking","music"]})

Now what will happen if I will try to create an Index on SSN Field with unique Property. If I will try to create an index with unique property as below, then I will get an error because SSN contains null for the last two documents and so SSN is not unique.

C#
db.Users.createIndex({SSN:1},{unique:true})

and error is:

C#
E11000 duplicate key error index: Test.Users.$SSN_1  dup key: { : null }

So what will be the solution? Can't I create a unique index on such records?

Hold on... we have a solution.. we have sparse property for such scenarios.

Sparse will tell the database that those documents should not be included in index where SSN is missing.

So cool.. It's time to create a index with sparse with unique property.

C#
db.Users.createIndex({SSN:1},{unique:true,sparse:true})

and this time, there will be no error while creating index with unique property because sparse is there.

3. Partial Index

This is a new born concept which comes with MongoDB 3.2. Sometimes, we want to create index with some specific condition. If we are creating index with some condition, then it is a partial Index.

Suppose I want to create an Index on Name field only when Age is greater than 30. We need to specify a condition while creating an Index as below:

C#
db.Users.createIndex(
   { Name: 1},
   { partialFilterExpression: { Age: { $gt: 30 } } }
)

To apply a condition, we use partialFilterExpression.

4. TTL Index

MongoDB has a special type of single Field Index named TTL Index. MongoDB uses this type of Index to remove document automatically after a certain period of Time. We use expireAfterSeconds option to provide the expiration Time.

I am creating a TTL Index on Age with 60 second expiring time as below:

C#
db.Users.createIndex( { "Age": 1 }, { expireAfterSeconds: 60 } ) 

It will delete this document after 60 seconds automatically. A background task runs in every 60 seconds that removes all the expired documents. So it might take some extra time to remove this document from collection. It depends also on workload of mongod instance so expired documents can be in collection beyond the specified amount of time.

Some Key Points

1. getIndexes()

If we want to see all the created indexes on a collection, we use getIndexes() method.

Syntax: db.CollectionName.getIndexes()

2. dropIndex()

To drop an Index, we use dropIndex() method.

Syntax: db.CollectionName.dropIndex({"Key":1 or -1})

Pass the key with 1 or -1 which we passed while creating index.

3. dropIndexes()

To remove all the Indexes from a collection, we use dropIndexes() method.

C#
db.CollectionName.dropIndexes() 

4. To Get All Indexes From a Collection

C#
db.getCollectionNames().forEach(function(collection) {
   index = db[collection].getIndexes();
   print("Indexes for " + collection + ":");
   printjson(index);
});

5. Rebuild an Index

To Rebuild an Index, we use reIndex() method as below:

C#
db.CollectioName.reIndex()

Limitations

  1. A single collection cannot have more than 64 indexes.
  2. Compound Index cannot have more than 31 fields.

Reference

History

  • 19th April, 2016: Initial version

Finally, if this is helpful and you liked it, please vote.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Hi Myself Vijay having around 7 years of experience on Microsoft Technologies.

Comments and Discussions

 
GeneralMy vote of 5 Pin
DataBytzAI23-Apr-16 5:47
professionalDataBytzAI23-Apr-16 5:47 
GeneralMy vote of 5 Pin
faraaz.aamir19-Apr-16 20:24
faraaz.aamir19-Apr-16 20:24 
QuestionMind blowing Pin
Member 1247122619-Apr-16 8:17
Member 1247122619-Apr-16 8:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.