Q:

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.
While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if there is a tool that could help me find out least recently used indices from all the tables.
For eg. , if table "A" has three indices defined "index_1", "index_2" and "index_3", and out of them "index_3" is used least frequently , assume its used every 1/10000 queries made on the table, then the output of the script or tool should be "index_3".
Is there a good way or a tool that could help me run this analysis on the database?
Thanks in advance.

A:

Starting with MySQL 5.6, the performance_schema instruments Table I/O, and computes aggregated statistics by table, and by index.
See table performance_schema.table_io_waits_summary_by_index_usage:
http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table
Finding the least recently used index involves time and timestamps.
The performance schema measure counting I/O against an index.  It can be used to find the least often used index, which in practice should be pretty close.
Full example here:
http://sqlfiddle.com/#!9/c129c/4

