Understand MongoDB Indexes in simple words

Indexes help us to get efficient execution of queries in MongoDB. If we don’t have indexes then MongoDB scan entire collection to match the query statement. So we create index to avoid heavy collection scan (i.e. scan every document). It is really beneficial when you have a huge number of documents in a collection.

Why should we use Indexes?

• It improves query execution
• If appropriate indexes exists, then only index scan(IXSCAN) will be performed by MongoDB
• It stores values in sorted format so helps a lot in conditional queries
• It scans whole collection(COLLSCAN) without indexes that usually cause performance issue

How Index works?

As discussed, when we create index MongoDB stores sorted keys(ascending or descending). Let’s say we create index on age in ascending order. If a query statement says “It requires persons with age less than 25” then it picks from first index to 25 that means we don’t need to scan all document in persons collection.

Default _id Index

When we create a collection, MongoDB creates one unique index by default on _id field. We cannot drop this index on the _id field.

{
 "v" : 2,
 "key" : {
   "_id" : 1
 },
 "name" : "_id_"
}

getIndexes()

It helps us to get all indexes created on a collection. I have created one collection named as “employees” for demonstration with a index on name field. We can notice that index on _id field is also available here.

db.employees.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "name" : 1
                },
                "name" : "name_1"
        }
]

Create an Index- createIndex()

We should create indexes only on those fields which are frequently used in query. {name:1} -> Ascending, {name:-1} -> Descending

db.employees.createIndex({name:1})
{
        "numIndexesBefore" : 1, // Default 
        "numIndexesAfter" : 2, 
        "createdCollectionAutomatically" : false,
        "ok" : 1
}

Index Creation Options

• If we are working with a huge collection and query operations are live then creating indexes can interrupt the performance. So we can use this option ( { background: true} )to avoid interruption.
• If we want to specify that value for a field are unique across the collection then we can use { unique: true }.
• We can also set custom name to indexes with { name: <index name>}

Note: These options can be placed in 2nd argument of createIndex().

db.employees.createIndex(
  { name: 1} , 
  { background: true}
)
db.employees.createIndex(
  { index: 1} , // Assume that index field is unique across the collection
  { unique: true}
)
db.employees.createIndex(
  { name: 1 } ,
  { name: "query for name" } // Custom Name
)

Compare the name of Index with or without name in option

 db.employees.createIndex({position:1},{name: "cutom position"})
{
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 3,
        "createdCollectionAutomatically" : false,
        "ok" : 1
}
> db.employees.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "name" : 1
                },
                "name" : "name_1" // Default index name
        },
        {
                "v" : 2,
                "key" : {
                        "position" : 1
                },
                "name" : "cutom position" // Custom name
        }
]

Analyze Query Performance using explain()

db.employees.find({…}).explain() => query planner details

db.employees.find({…}).explain(‘executionStats’) => More detailed information related to query performance

Note: Please try this and observe the details with or without index fields.

Drop Indexes

> db.employees.dropIndex({position: 1}) // Use this to delete single index
{ "nIndexesWas" : 3, "ok" : 1 }

> db.employees.dropIndexes() // To delete all indexes on a collection
{
        "nIndexesWas" : 2,
        "msg" : "non-_id indexes dropped for collection",
        "ok" : 1
}

> db.employees.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" } ]

Note: We can see default index is still there.