4

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.

1 Answer 1

9

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

2
  • Isn't this unhelpful when your entire DB fits into memory, so there'd be no IO after long enough?
    – aehlke
    Sep 13, 2016 at 20:46
  • 2
    @aehike,Table I/O is measured at the sql layer / storage engine interface. File I/O is below, at the storage engine / File system interface. Even if the entire DB is in RAM, and the file system is never hit, the MySQL server will still call the storage engine (innodb, etc) for rows, and table I/O will be counted here.
    – Marc Alff
    Sep 13, 2016 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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