MySQL Identifying unused indexes

Share via:

Dear Readers,

In this article, we will see MySQL Identifying unused indexes.

Identifying unused indexes:
MySQL doesn’t provide completely reliable options to get the accurate index usage details. Below listed are the some of the options available and some of the options are version specific or may require additional plugins to gather the data.

Performance schema:
This approach works by fetching the details from the ‘performance_schema’.’table_io_waits_summary_by_index_usage’. To do this we need to have performance schema enabled in our production environment and is supported in both Percona and stock MySQL. This approach is less safe as because it assumes an index is unused if it has never caused IO wait since last restart.

Note: Data collected using this approach is reliable only if the MySQL service has been up and running for long time so all the queries used by the application on regular basis and jobs that execute daily, nightly, weekly and monthly are executed at least once. Before proceeding with this, make sure to check how long mysql service has been up.


Sys Schema:
This view reads the data from ‘performance_schema.table_io_waits_summary_by_index_usage’. Sys schema is just a way to read the information stored in performance schema tables in more clear and easy way. Since this reads the data from performance schema, it identifies index usage in the same manner as performance schema so it is reliability is same as previous approach.

Information Schema and innodb_index_stats:
This approach calculates index usage by comparing  ‘mysql’.’ innodb_index_stats’ and ‘INFORMATION_SCHEMA’.’INDEX_STATISTICS’ tables. The prerequisite is to have the ‘userstat’ variable turned ON and compare the output of the two.
One of the limitations on User Statistics is, it can only track direct lookups on index keys but not lookups done during constraint checks so foreign keys will be marked as unused indexes.
The next limitation is this approach don’t provide index stats on partitioned tables.
Note: If you wish to use this approach, you must set userstat=on and let the database run for good amount of time so it can collect the details.


Percona specific:
This tool connects to a MySQL database server, reads through slow query log, and uses EXPLAIN to ask optimizer how it will execute each query. When it is finished, it prints out a report on indexes that the queries didn’t use. The tool works only with slow query log but we can feed other logs by converting the logs to the format of that of slow query log by using pt-query-digest tool. After it reads all the events in the log, the tool prints out DROP statements for every index that was not used. It skips indexes for tables that were never accessed by any queries in the log, to avoid false-positive results.
If you don’t specify –quiet, the tool also outputs warnings about statements that cannot be EXPLAINed and similar. These go to standard error.
Progress reports are enabled by default (see –progress). These also go to standard error.
Note: This command should be executed from the linux command line and not on MySQL shell.


Thank you for giving your valuable time to read the above information.
Follow us on 
Facebook Page KTexperts
Linkedin Page   : KT EXPERTS

Follow Me On
Linkedin :Anban Malarvendan

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.38 out of 5)

Add Comment