Dropping unused indexes

January 15, 2009

Author

Peter Zaitsev

Insight for DBAs

Share this 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:

<br>mysql> select concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND s.TABLE_NAME=iz.TABLE_NAME AND s.INDEX_NAME=iz.INDEX_NAME) WHERE iz.TABLE_SCHEMA IS NULL  AND s.NON_UNIQUE=1 AND s.INDEX_NAME!='PRIMARY' and (select rows_read+rows_changed from information_schema.table_statistics ts where ts.table_schema=s.table_schema and ts.table_name=s.table_name)>0) d group by table_schema,table_name;<br>+-----------------------------------------------------------+<br>| stmt                                                      |<br>+-----------------------------------------------------------+<br>| alter table board.country_language drop index country_id; |<br>+-----------------------------------------------------------+<br>1 row in set (0.99 sec)<br><br><br>

1

mysql> select concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND s.TABLE_NAME=iz.TABLE_NAME AND s.INDEX_NAME=iz.INDEX_NAME) WHERE iz.TABLE_SCHEMA IS NULL AND s.NON_UNIQUE=1 AND s.INDEX_NAME!='PRIMARY' and (select rows_read+rows_changed from information_schema.table_statistics ts where ts.table_schema=s.table_schema and ts.table_name=s.table_name)>0) d group by table_schema,table_name; +-----------------------------------------------------------+ | stmt | +-----------------------------------------------------------+ | alter table board.country_language drop index country_id; | +-----------------------------------------------------------+ 1 row in set (0.99 sec)

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.