LessThanDot

This is my fourth MongoDB post, in the first post we looked at how we can install MongoDB as a Windows Service, In the second post we looked at how we could do UPSERTs with MongoDB, In the third post we looked at how to sort results in MongoDB. This post is about indexing in MongoDB, we are going to take a look at how to create indexes and how to see if the indexes are being used by MongoDB.

Every index that you create in MongoDB is a secondary index, this is because MongoDB creates the default _id index for all collections. The _id index is a unique index on the _id field, you cannot delete the index on _id.

MongoDB indexes use a B-tree data structure

Besides a regular one field index, you can also create the following indexes in MongoDB

Indexes on Embedded Fields
Compound Indexes
Multikey Indexes
Unique Indexes
Sparse Indexes

Before you go crazy and start adding indexes on every possible field in your collection, keep in mind that just like in regular databases, the more indexes you have the slower you write operations will be. Every update and insert will be a little slower because the indexes will have to be maintained.

Some limitations:

A collection can’t have more than 64 indexes.

Index keys can’t be larger than 1024 bytes. This includes the field value or values, the field name or names, and the namespace.

Let’s go take a look at some of these indexes

Creating A Simple Index in MongoDB

Let’s insert some data before we create the index

db.Indexing.insert( { name : "Denis", age : 20 } )
db.Indexing.insert( { name : "Abe", age : 30 } )
db.Indexing.insert( { name : "John", age : 40 } )
db.Indexing.insert( { name : "Xavier", age : 10 } )
db.Indexing.insert( { name : "Zen", age : 50 } )

Now let’s run a simple query that will return all rows where the name is Denis and we want to see what the engine is doing. You can use explain to return the plan

db.Indexing.find({name: "Denis"}).explain()

Here is what you get back

{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "Denis:27017"
}

Let’s add an index

db.Indexing.ensureIndex({name: 1});

What we did was, we created an index on name and it is sorted ascending, if you want descending, you would use -1 instead of 1

Now if we add run the same command from before with explain

db.Indexing.find({name: "Denis"}).explain()

Here are the results

{
        "cursor" : "BtreeCursor name_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 1,
        "indexBounds" : {
                "name" : [
                        [
                                "Denis",
                                "Denis"
                        ]
                ]
        },
        "server" : "Denis:27017"
}

As you can see several things are different. Instead of a BasicCursor, we now use a BtreeCursor

Also instead of

"indexBounds" : {
        
}

we now see

"indexBounds" : {
      "name" : [
                 [
                      "Denis",
                      "Denis"
                 ]
               ]
    },

Dropping an Index in MongoDB

You can drop an index by specifying a field in a collection

db.Indexing.dropIndex({name: 1});

Here is the output

{ "nIndexesWas" : 2, "ok" : 1 }

You can also drop all indexes for a collection in one shot

db.Indexing.dropIndexes()

Here is the output

{
        "nIndexesWas" : 1,
        "msg" : "non-_id indexes dropped for collection",
        "ok" : 1
}

As we mentioned before all _id fields have an index by default and those don’t get dropped

Creating A Unique Index in MongoDB

Let’s now create an index on name again but this time we will make it unique., The syntax is the same as with the index we created before with the addition unique: true

If you didn’t drop the index we created before, drop it first and then execute the following

db.Indexing.ensureIndex({name: 1}, {unique: true});

Now if we try to insert the same name again, you will see that we get an error

db.Indexing.insert( { name : "Denis", age : 20 } )

Here is the output

_E11000 duplicate key error index: Indexing.Indexing.$name1 dup key: { : “Denis” }

As you can see it is pretty easy to create a unique index

Creating A Compound Index in MongoDB

Creating an compound index is pretty straightforward as well, you just add the other fields. So if we want to create an compound index on name and age then you would do it like this

db.Indexing.ensureIndex({name: 1, age : 1});

Just like in a regular index, you use 1 for ascending and -1 for descending

To create an compound unique index, you would just add unique: true

db.Indexing.ensureIndex({name: 1, age : 1}, {unique: true})

The same rules like in a regular RDBMS when an compound index is used apply. If you supply the first field ot the first field and subsequent fields, then the index will be used, otherwise the index will not be used. You can easily test this.

Search for name which is the first field in the compound index

db.Indexing.find({name: "Denis"}).explain()

Here is the output, as you can see the index is used

{
        "cursor" : "BtreeCursor name_1_age_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "name" : [
                        [
                                "Denis",
                                "Denis"
                        ]
                ],
                "age" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "server" : "Denis:27017"
}

Now search for name which is the second field in the index

db.Indexing.find({age: "20"}).explain()

Here is the output for that

{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 5,
        "nscanned" : 5,
        "nscannedObjectsAllPlans" : 5,
        "nscannedAllPlans" : 5,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "Denis:27017"
}

As you can see, no index was used

If you want to dive deeper into Indexing in MongoDB I would suggest you take a look at the documentation on the MongoDB site, they have an excellent section on Indexing with many more examples than I have given here. I didn’t cover Sparse Indexes, Indexes on Embedded Fields, Multikey Indexes, TTL Indexes, Geospatial Indexes or Geohaystack Indexes. You can find the documentation here: http://docs.mongodb.org/manual/core/indexes/

Indexes in MongoDB: A quick overview

Creating A Simple Index in MongoDB

Dropping an Index in MongoDB

Creating A Unique Index in MongoDB

Creating A Compound Index in MongoDB

About SQLDenis