A quick guide to MongoDB Indexing

Databases are the backbone of any modern application, be it mobile or web. The database helps store useful information so that it can be queried whenever needed. Let’s assume the database is like a book. It’s easy to read through and find certain things that you like if a book has fewer pages. But for a book with a large number of pages, it will take a substantial amount of time to revisit your favourite quotes, act, chapter, etc. To our rescue, we have pages like “Table of Contents”, “Index”  that help us traverse through pages easily. 

What is indexing?

Creating an index on a field creates another data structure that holds the field value and a pointer to the original record. In MongoDB whenever an index is created it is sorted in ascending or descending order. Given below is an example of how you will create an index on a User’s collection sorted by descending order of age.

> db.User.createIndex({age: -1})

Why should we use indexes?

Indexes help in the fast retrieval of documents(records). They can drastically reduce query time for large data sets. Without indexes MongoDB performs a collection scan i.e. MongoDB will scan every document and it can be expensive if your application doesn’t need every data or a good chunk.

Example: Fetching a single user’s details from user collection using an id or email that is not indexed. Mongo will scan every document every time whenever that particular query is executed.

Side Note

• Command to get all indexes on a particular collection
  

> db.Contacts.getIndexes()

• To run a diagnostic on a particular query
  

> db.contacts.explain ('executionStats').find()

Types of indexes in MongoDB

• Single field index: MongoDB allows the creation of an index on a single field and for sort operations. For single-field indexes sort order defined doesn’t matter because MongoDB can traverse the index in both directions.
  

db.contacts.createIndex({email: 1})

• Compound Index: MongoDB supports indexes on multiple fields for instance {email: 1, age: -1}. The order of fields in a compound key has significance. Let’s execute it and see what it actually means. On execution of the following query:
  

> db.User.explain ('executionStats').find().sort({name: 1, age: -1})

Under queryPlanner > winningPlan > inputStage > stage the value is “IXSCAN” which means an indexed scan was the winning plan and not the whole collection was searched.  Let’s now execute the same query but now with fields reversed.

> db.User.explain ('executionStats').find().sort({age: -1, name:1})

And the result was as following:

Here in the “winning plan”, we can see the whole collection was scanned.

Note: For a query to use a compound index for a sort, the specified sort direction for all keys in the cursor.sort() document must match the index key pattern or match the inverse of the index key pattern. This means for the above example both {email: -1, age: 1} and {email: 1, age -1} is supported but not {email: 1, age: 1} or {email: -1, age: -1}.
 

• Partial Index: Partial indexes are somewhat like compound indexes and sparse indexes but with a little more power. For partial indexes, we mention conditions or expressions based on which documents are indexed. However, for the sparse index, if the indexed field is missing then that document is skipped from the indexing process i.e sparse indexes only check for the existence of a key.

Query to create Partial Index:

> db.User.createIndex ({name: 1, age: -1}, {partialFilterExpression: {score: {$gt: 33} }})

• Query Coverage: For the index created above MongoDB will not use the index if the result is an incomplete set. For example: On executing query mentioned below.
  

> db.User.explain ('executionStats'). find({name: 'Mary', score: {$gt: 32} })

In the execution statistics mentioned below, the winning plan was “COLLSCAN” because we created a partial index for indexing documents having a “score” above 33, and our query is searching for a value above 32 therefore MongoDB rejects index scan and performs collection scan to avoid the incomplete result.

• TTL(Time-to-live) index: In the case of collections where documents should be deleted after a period of time. TTL index is the best way to go about it. TTL index works on date fields. To create a TTL index 2 parameters are necessary, firstly a date field and secondly the amount of time after which indexed documents will be automatically removed. Query to create a TTL index.
  

db.User.createIndex( {"createdAt": 1}, {expireAfterSeconds: 3600} )

• Text Indexes: Text indexes are used to index 
  

db.Books.createIndex ({name: 'text''})

To search a collection having a text-based index, the query looks something like this:

Word Exclusion: MongoDB also has the feature to exclude words from the search query. Simply add subtract before the word that is to be excluded.

For example: 

> db.Books.find({$text:  {$search: 'age -number'} }). pretty () 

Result: The document having the name property “Age is just a number” is excluded. 

Using indexes carefully

Now I get it. If we index all our fields it will make our application fast. Not true!!!

Indexes come at a cost, it takes substantial time when creating new documents when a collection have various indexes set on it. All the existing indexes will be re-calibrated every time a document is added. Also one should not use indexes if a query returns a major chunk of your collection because it adds an extra step of going through indexed fields and fetching corresponding documents.

Conclusion

Above mentioned indexes are some of the widely used indexing methods. Indexes can be introduced and used at any point of time in the development cycle. So, play with indexes create them, drop them and find out their execution status to choose what best works for your application.

Happy Coding!!!