optimization - How do you identify unused indexes in a MySQL database?

Question

I have recently completely re-written a large project. In doing so, I have consolidated great number of random MySQL queries. I do remember that over the course of developing the previous codebase, I created indexes on a whim, and I'm sure there are a great number that aren't used anymore.

Is there a way to monitor MySQL's index usage to determine which indexes are being used, and which ones are not?

Answer 1

I don't think this information is available in a stock MySQL installation.

Percona makes tools and patches for MySQL to collect index usage data.

See:

• User Statistics (and Index Statistics)
• How expensive is USER_STATISTICS?
• pt-index-usage

See also:

• New INDEX_STATISTICS table in the information_schema
• check-unused-keys: A tool to interact with INDEX_STATISTICS
• New table_io_waits_summary_by_index_usage table in performance_schema in MySQL 5.6

You may also be interested in a Java app called MySQLIndexAnalyzer, which helps to find redundant indexes. But this tool doesn't have any idea which indexes are unused.