EmergencyEMERGENCY? Get 24/7 Help Now!

Dropping unused indexes

 | January 15, 2009 |  Posted In: Insight for DBAs


Vadim wrote some time ago about how to find unused indexes with single query.

I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim’s query to generate ALTER TABLE statements automatically. I also made it to look only at tables which were accessed:

I however would warn against using it blindly in production. It is possible some indexes were not used since startup but are still used… for example if you’re having monthly billing or something like it.

However it is very helpful for testing allowing to drop all potentially not needed indexes so you can perform proper QA and ensure you really did not drop anything you needed. In such case it would make sense to run this query in production but then do changes in test envinronment first.

Note this query requres MySQL with Percona Extensions and user statistics running.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master’s Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • I had a problem with my database searching in DVLA number plates ’35785613′ Records altogather 1.6 GB database size when I delete redudant indexes, my search was improved.

  • Is it possible to find unused index also on a MyISAM table? The above query says “#1109 – Unknown table ‘index_statistics’ in information_schema” at my system (MySQL 5.0.67).

  • Plus doesn’t dropping the index lock the table for the duration?

    I remain skeptical of the utility of dropping unused (or rarely used) indexes.

    The load on most web applications is heavily weighted towards SELECT operations and there’s little downside to having “too many” indexes in that case. Only thing its really going to cost you is insert/update/delete time and some disk space.

    To be sure, there will be cases where you’ve got an update bound workload and removing useless indexes is the solution, but, at least for the kind of applications I work with, that’s very much the edge case.

  • Jan,

    This works for both MyISAM and Innodb tables. As I mentioned this requires Percona Extensions to work. See the link in the bottom of the page.

  • Pat,

    Unused indexes are dead weight. How much it hurts it is application dependent but if you do not have a discipline dealing with it over years you may get too much of it.

    Unused indexes slow down inserts but they also hurt selects because they waste time in caches. They also make MySQL optimizer to consider more choices than it needs to.

    Finally disk space also means less hardware requirements and faster backups and other operations (such as cloning the slave)

    Of course your millage may vary.

Leave a Reply