You have to be careful what you wish for. The signal:noise ratio of finding out which indexes are “unused” is low. This isn’t obvious at first glance, but upon deeper inspection, there’s often not much to be gained, and it can even harm you. Let’s look at why.
How to Find Unused Indexes
The typical way to figure out which indexes aren’t used is to examine index-usage counters over a period of time. The database itself can provide these statistics: PostgreSQL has built-in views that can expose them, and in MySQL there’s similar views as well as community patches that originally came from Google. There’s a way to get the stats in MongoDB too.
What advice do people want about unused indexes? It typically boils down to:
- Tell me indexes that don’t seem to have any activity according to the statistics.
- Prioritize these indexes by the size or …