Adding Indexes To Mongo + A Case Study
Tags: programming, benchmarks, database, indexes
Published: 2018-12-24 21:33:19

---
title: Adding Indexes To Mongo + A Case Study
published: true
tags: Programming,benchmarks,databases,indexes
canonical_url: https://www.stephencharlesweiss.com/2018/11/30/adding-indexes-to-mongo-a-case-study/
---

I’ve been working on projects with larger data sets recently and have begun to notice that not _all_ queries resolve in one or two milliseconds.

As I wrote in [Indexing Databases – A Postgres Example](https://wp.me/p6u9oI-gW), adding indexes can speed up queries, but come at a cost of inserting / updating times.

This time around, I was working with MongoDB and wanted to understand how indexing worked. While the principles hold, the details vary.

Still – the conclusion’s the same: if you’re reading more than you’re writing and can come up with a sound index target, it can be a valuable tool in your tool belt.

Let’s dig in.

# Common Commands

The three most useful beginner commands I found were create, get, and drop.

{% gist https://gist.github.com/stephencweiss/04c3a9e1e0902b6c049e5385c684cb17 %} 

## Create an Index

`db.collection.createIndex( { <field_name> : <order> [, <field_name> : <order> ...] } )`

When creating an index – you can specify the field(s) and the order.

The order is `1` for ascending and `-1` for descending.

The ordering is done sequentially, so the first field listed is sorted first, then the second, and so on.

{% gist https://gist.github.com/stephencweiss/0b9b7e1ee64abdd5e482546c04c68aaf %}

## Review Indexes

`db.collection.getIndexes()`

To see the indexes on a collection, use the `getIndexes()` method.

This is helpful when confirming the index is on the expected attribute of the document and to see which queries have been optimized.

{% gist https://gist.github.com/stephencweiss/3f4095da701b0f8191e9c006b74f289f %}

## Drop Indexes

`db.collection.dropIndexes(["<index_name>"])`

If you want to remove an index, you can use the `dropIndexes()` method.

The optional parameter allows for specifying _which_ index should be dropped. The name can be retrieved using the `getIndexes()` method.

If no name is specified, all _non_–`_id` indexes will be dropped.

{% gist https://gist.github.com/stephencweiss/aab22217b5698f7cba0086adb9c98b2d %}

# Case Study

So what do indexes actually get us?

To test this, I created a sample database (`trailblazers`) and collection (`descriptions`) and generated 10m rows of data.

I then compared the performance of the same lookup query on the database pre- and post-index. This is not a scientific study, but at least when querying on the indexed attribute, query times fell 99.99% from 15000+ ms to ~1 ms.

Let’s take a look.

These times were found using the `.explain(“executionStats”)` method.

## Without An Index
{% gist https://gist.github.com/stephencweiss/6746fe58f40d0a056d63e74b8c483af7 %}


## With An Index

{% gist https://gist.github.com/stephencweiss/828e54a0745af11d399bfac670d79d55 %}

## Conclusion

The index created a much more efficient query because Mongo knew exactly where to go to find the documents in question.

Without the index, the query reviewed all 10 million + documents. With the index, that number was cut to 10.

Another way to think about this is that the index changed the time complexity of the query from linear to constant.

As the database continues to grow, indexed fields can be found in constant time because the index offers a pointer.

That direction comes at a cost of a slower insert / update time, but if the database has more reads than writes, that trade off can be worth it quickly.

# Further Reading

[MongoDB docs](https://docs.mongodb.com/manual/indexes/#create-an-index)