October 21, 2014

Dropping unused indexes

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.

About 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.

Comments

  1. PHP Programmer says:

    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.

  2. Jan says:

    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).

  3. pat says:

    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.

  4. peter says:

    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.

  5. peter says:

    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.

  6. Don’t forget about extraordinary circumstances, such as restoring from backups, checking for corruption after a crash, etc.

Speak Your Mind

*