MySQL Identifying unused indexes

Share via:

Post Views: 0

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.

SHOW GLOBAL STATUS LIKE 'Uptime';

1	SHOW GLOBAL STATUS LIKE 'Uptime';

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name, index_name;

SELECT object_schema, object_name, index_name

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL AND count_star = 0

ORDER BY object_schema, object_name, index_name;

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND index_name != 'PRIMARY'
AND count_star = 0
AND object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY object_schema, object_name;

SELECT object_schema, object_name, index_name

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL

AND index_name != 'PRIMARY'

AND count_star = 0

AND object_schema NOT IN ('mysql', 'performance_schema')

ORDER BY object_schema, object_name;

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.

SELECT * FROM sys.schema_unused_indexes;

1	SELECT * FROM sys.schema_unused_indexes;

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.

SET GLOBAL userstat=on;

1	SET GLOBAL userstat=on;

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS
ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND
s.TABLE_NAME=INDXS.TABLE_NAME AND
s.INDEX_NAME=INDXS.INDEX_NAME)
WHERE INDXS.TABLE_SCHEMA IS NULL
AND lower(s.TABLE_SCHEMA) like '%schema%name%'
ORDER BY 1,2,3;

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME

FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS

ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND

s.TABLE_NAME=INDXS.TABLE_NAME AND

s.INDEX_NAME=INDXS.INDEX_NAME)

WHERE INDXS.TABLE_SCHEMA IS NULL

AND lower(s.TABLE_SCHEMA) like '%schema%name%'

ORDER BY 1,2,3;

SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='table_name';
SELECT * FROM mysql.INNODB_INDEX_STATS where table_name = 'table_name';

1 2	SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='table_name'; SELECT * FROM mysql.INNODB_INDEX_STATS where table_name = 'table_name';

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.

pt-index-usage /path/to/slow.log

1	pt-index-usage /path/to/slow.log

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