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:

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

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

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

The returned document will look like this:

{
"name" : "test",
"key" : {
"key" : 1
},
"host" : "xxx:27017",
"accesses" : {
"ops" : NumberLong(145),
"since" : ISODate("2017-11-25T16:21:36.285Z")
}
}

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.

How to Find Unused Indexes in MongoDB - Improve Your Write PerformanceClick To Tweet

Dropping Unused Indexes in MongoDB

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

db.collection.dropIndex( "<index name>"); or
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.

 

Learn more about ScaleGrid


Dharshan is the founder of ScaleGrid.io (formerly MongoDirector.com). He is an experienced MongoDB developer and administrator. He can be reached for further comment at @dharshanrg


  • david modica

    what is the value being replaced by amp in the above:

    {“name”: “&amp;lt;indexname&amp;gt;”}}

  • https://scalegrid.io Dharshan

    Sorry…encoding issue with “”. Issue has been fixed

  • Kay Agahd

    If you want to have a tabularly, sortable and filterable overview of the index stats of all your mongodb installations (among other features), you might be interested in this open source project:
    https://github.com/idealo/mongodb-slow-operations-profiler

4 Shares
+1
Tweet
Share
Share4
Pin