How to recognize what indexes are not used?

Question

I have a table in MySQL with 7 indexes, most of them are on more than one column. I think here is too much indexes. Is there any way to get statistics of what indexes are used more by all thousands of queries to this database and what are less worthy so I know what index to consider to remove in first place?

Answer 1

This isn't a specific way to do that, but you might be able to tell. Try EXPLAIN SELECT... and it will show you what tables are being used, how many rows, etc. etc. This should give you enough data to speed up your table

Answer 2

You can try to determine which indexes are used at all.

However, as far as a list by usefulness, sorry, there's no built-in way to do that, and I don't think the Query Analyzer even does that.

It seems like you might need to do an audit to determine the queries that are in use that affect that table, and do an explain on them to see if they use any of your indexes.

There is a bug report requesting this feature.

Answer 3

Turn on general query logging in production. Yes, I know it's a pain, and I know it will impact your production server, but BITE THE BULLET. It will be useful later. Leave it turned on indefinitely if possible (of course ensure that log rotation, compression and expiry works and definitely monitor disc space usage; tell Ops how to turn it off if bad things happen)

Then you can start playing with tools like mk-query-digest which will run EXPLAIN on all of your queries (do this against a non-production database, of course, but use a recent dump of your production db).

This will enable you to tell which indexes are being used and how often. At least, if EXPLAIN isn't lying and your test server uses the same plan as production.

Or at least, it mostly will. EXPLAIN doesn't yet tell you how DELETE, UPDATE queries work.

Answer 4

You should check out the Percona patches or their server builds. They have added tables to the INFORMATION_SCHEMA so you can find unused indexes.

http://www.mysqlperformanceblog.com/2008/09/12/unused-indexes-by-single-query/ http://www.mysqlperformanceblog.com/2009/01/15/dropping-unused-indexes/