Menu
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.
579
You can list a table's indexes with the mysqlshow -k db_name tbl_name command. In MySQL 8.0. 30 and later, SHOW INDEX includes the table's generated invisible key, if it has one, by default.
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
In MySQL, an index block is usually 1,024 bytes and the data pointer is usually four bytes. For a 500,000-row table with a key value length of three bytes (the size of MEDIUMINT ), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.
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
Addition to @user1970667's answer, you may also use:
select * from sys.schema_unused_indexes;
to get a list of unused indexes.
11
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.
5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
