1

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 Null Pointer
  • 2,261
  • 3
  • 26
  • 28
  • If they are duplicates then why do you need to worry about which one to drop? If they are just different in the order of the fields indexed, then you should inspect your queries, choose the index to use and update your queries which fields in the WHERE / JOIN statements are not in the same ordering. – sn00k4h Oct 04 '12 at 06:22
  • I should probably have been more verbose in asking this. My task is to clean up indices duplicate as well as least frequently used ones. I was able to run a tool which informed me about the duplicate indices in each table, I was wondering if there was a similar tool available for finding out least recently used index as well. Hope that clarifies. – A Null Pointer Oct 04 '12 at 07:02

1 Answers1

1

Starting with MySQL 5.6, the performance_schema instruments table io, 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.

What the performance schema measure is counting io against an index, so 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

Note: This question is also duplicated in

https://dba.stackexchange.com/questions/25406/find-least-recently-used-mysql-table-index

Community
  • 1
  • 1
Marc Alff
  • 8,227
  • 33
  • 59
  • Thanks @MarkAlff . I had initially posted this question on DBA stack-exchange forum and then migrated it to here in the interest of the stack-overflow community. – A Null Pointer Oct 04 '12 at 08:45