How to Find Unused Indexes in MongoDB?

In this post, we'll show you how to find unused indexes in MongoDB, check usage statistics on your index collection, and how to drop your unused indexes.

Actively managing MongoDB database indexes over several application releases can be challenging for development teams. Requirements often change with every release, where several new indexes are may be added and old indexes might become abandoned. Over time, this makes it difficult to keep track of which MongoDB indexes are being used and which ones are now unnecessary.

Indexes have a big impact on write performance - every time there's a write to a collection, the relevant indexes need to be updated. Lack of indexes manifests immediately and slows down the query - unused indexes are more subtle and need to be actively pruned out to improve write performance. More information can be found here - Perils of building indexes on MongoDB.

In earlier versions of MongoDB, there was no easy way to determine if an index was not being used. However, starting in version 3.2, MongoDB added support for the $indexStats operator which lets you gather statistics about your index usage.

Finding Unused Indexes in MongoDB

To check usage statistics of a particular index on a collection, you can use this command:

1 db.collection.aggregate([{$indexStats: {}}, {$match: {"name": "<indexname>"}}])

To get the statistics for all the indexes of a collection:

1 db.collection.aggregate([{$indexStats:{}}])

The returned document will look like this:

1 {
2 "name" : "test",
3 "key" : {
4 "key" : 1
5 },
6 "host" : "xxx:27017",
7 "accesses" : {
8 "ops" : NumberLong(145),
9 "since" : ISODate("2017-11-25T16:21:36.285Z")
10 }
11 }

The two important fields to note here are:

1. Ops

   This is the number of operations that used the index.

2. Since

   This is the time from which MongoDB gathered stats, and is typically the last start time.

Dropping Unused Indexes in MongoDB

Once you've identified and verified unused MongoDB indexes, you can drop the index using the code below:

1 db.collection.dropIndex( "<index name>")  or
2 db.collection.dropIndex("{key1:1.....}")

As always please verify that you are dropping the right index before you proceed with the drop operation.

If you have any questions on finding unused indexes in MongoDB, leave a comment below or reach out to us at support@scalegrid.io.