Find out usage statistics of MySQL indices?

mysql indexing statistics usage-statistics
R_User picture R_User · Jan 3, 2013 · Viewed 15.4k times · Source

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.

Answer

user1970667 picture user1970667 · Apr 4, 2017

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

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
How to see indexes for a database or table in MySQL?

How do I see if my database has any indexes on it? How about for a specific table?

Read More
Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables? How would I use them?

Read More