31

Is it possible to find out how often an index of a MySQL table was used?

I have several indices for a table and I'd like to find out, if there are indice which are not used by MySQL.

R_User
  • 10,682
  • 25
  • 79
  • 120

3 Answers3

49

Yes, it is. You should query Performance Schema:

select * from performance_schema.table_io_waits_summary_by_index_usage
where object_schema = 'your_schema'

The count_star column show how many times each index was used since MySQL was started. If you add the following, you got the never used indexes:

and count_star = 0
user1970667
  • 491
  • 4
  • 3
  • Valid for mySQL version 5.5.3 or better and only if enabled in my.cnf see https://dev.mysql.com/doc/refman/5.5/en/performance-schema.html – Peter Brand May 19 '17 at 20:34
  • You just made my day. I was learning about MongoDB and saw they have this feature and had to check if MySQL did as well. Lets just say i have some cleanup to do. – BilliD Jul 31 '19 at 19:37
15

Addition to @user1970667's answer, you may also use:

select * from sys.schema_unused_indexes;

to get a list of unused indexes.

Allen King
  • 2,372
  • 4
  • 34
  • 52
  • Note: I guess its apparent but - `This view is most useful when the server has been up and processing long enough that its workload is representative.`. I guess the same applies when querying `performance_schema` – kiran01bm Aug 12 '21 at 03:58
  • Yup. Production servers run for a long time without a restart. – Allen King Aug 31 '22 at 02:58
5

NOTE: This answer is no longer valid as of 5.5.3!

See https://stackoverflow.com/a/43205887/1251127

Original answer below.

Currently, MySQL does not offer statistics on index usage.

One way to generate those stats on your own would be to log all queries (Be careful of the extra i/o here) and parse it out. Percona has a nice tool for this, pt-index-usage.

ESG
  • 8,988
  • 3
  • 35
  • 52