Dropping unused indexes

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

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

Leave a Reply

Your email address will not be published. Required fields are marked *