Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.