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:
1 2 3 4 5 6 7 | 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.
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.
Don’t forget about extraordinary circumstances, such as restoring from backups, checking for corruption after a crash, etc.
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.