From ee571871c46e00659204f4691a96667ec1d418ca Mon Sep 17 00:00:00 2001 From: Gillian Gunson Date: Fri, 2 Sep 2016 16:52:10 -0700 Subject: [PATCH] added join to statistics for schema_unused_indexes for unique check --- views/p_s/schema_unused_indexes.sql | 24 +++++++++++++++--------- 1 file changed, 15 insertions(+), 9 deletions(-) diff --git a/views/p_s/schema_unused_indexes.sql b/views/p_s/schema_unused_indexes.sql index 2eff388..a8efc79 100644 --- a/views/p_s/schema_unused_indexes.sql +++ b/views/p_s/schema_unused_indexes.sql @@ -22,7 +22,7 @@ -- you should ensure that the server has been up for a representative amount of -- time before using it. -- --- PRIMARY (key) indexes are ignored. +-- PRIMARY (key) indexes and unique indexes are ignored. -- -- mysql> select * from schema_unused_indexes limit 5; -- +--------------------+---------------------+--------------------+ @@ -45,12 +45,18 @@ VIEW schema_unused_indexes ( object_name, index_name ) AS -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 - AND object_schema != 'mysql' - AND index_name != 'PRIMARY' +SELECT t.object_schema, + t.object_name, + t.index_name + FROM performance_schema.table_io_waits_summary_by_index_usage t + INNER JOIN information_schema.statistics s + ON t.object_schema = s.table_schema + AND t.object_name = s.table_name + AND t.index_name = s.index_name + WHERE t.index_name IS NOT NULL + AND t.count_star = 0 + AND t.object_schema != 'mysql' + AND t.index_name != 'PRIMARY' + AND s.NON_UNIQUE = 1 + AND s.SEQ_IN_INDEX = 1 ORDER BY object_schema, object_name;