Monitor unused indexes in MySQL

Question

I have a set of servers where the maintenance query execution takes long. This is due to the indexes that are created by the developers at different points. Is there any way to monitor the unused indexes that adversely affect the execution.

Answer 1

For mysql 5.6 before

By Default package of mysql does not have much statistics for analysis but there are some unofficial patches by which you can analyze.

like userstats http://ourdelta.org/docs/userstats

By This patch you can analyze the stats and unused indexes.

Follow the below links to do that

https://www.percona.com/blog/2012/06/30/find-unused-indexes/

In MySQL 5.6 and later, the PERFORMANCE_SCHEMA tracks index IO. If an index has no IO activity, it has not been used.

The sys schema now provides a convenient view to make it easier to identify unused indexes:

http://www.markleith.co.uk/2011/04/18/monitoring-table-and-index-io-with-performance_schema/

DROP VIEW IF EXISTS unused_indexes;

CREATE VIEW unused_indexes AS
SELECT object_schema,
   object_name,
   index_name
 FROM performance_schema.table_io_waits_summary_by_index_usage 
 WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;

Note that this shows indexes that haven't been used since the last restart of mysqld. The PERFORMANCE_SCHEMA statistics are reset at startup time.