MySql - find redundant indexes

Question

is there a tool that spots redundant indexes in MySql?

e.g. if I have the following indexes:

index1(col1)
index2(col1,col2)

then index1 should be flagged as redundant.

any ideas?

Answer 1

You can use pt-duplicate-key-checker from Percona Toolkit. You can also use the common_schema by Shlomi Noach.

Answer 2

Percona tool kit will I think, but I used these views by Roland Bouman

I've added them below:

CREATE OR REPLACE VIEW I_S_INDEXES
AS
SELECT TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE
, GROUP_CONCAT(
CONCAT('`',COLUMN_NAME,'`')
ORDER BY IF( INDEX_TYPE = 'BTREE' -- when BTREE then
, SEQ_IN_INDEX -- column order is important
, 0) -- else
, COLUMN_NAME -- only column content
) COLUMNS
FROM information_schema.STATISTICS
GROUP BY TABLE_SCHEMA
, TABLE_NAME
, INDEX_NAME
, INDEX_TYPE
, NON_UNIQUE
;
CREATE OR REPLACE VIEW
I_S_REDUNDANT_INDEXES
AS
SELECT l.TABLE_SCHEMA
, l.TABLE_NAME
, CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN GREATEST(l.INDEX_NAME, r.INDEX_NAME)
ELSE l.INDEX_NAME
END REDUNDANT_INDEX_NAME
, GROUP_CONCAT(
DISTINCT
CASE
WHEN l.COLUMNS = r.COLUMNS
AND (l.IS_UNIQUE = r.IS_UNIQUE)
THEN LEAST(l.INDEX_NAME, r.INDEX_NAME)
ELSE r.INDEX_NAME
END
) INDEX_NAME
FROM I_S_INDEXES l
INNER JOIN I_S_INDEXES r
ON l.TABLE_SCHEMA = r.TABLE_SCHEMA -- index on the same table
AND l.TABLE_NAME = r.TABLE_NAME
AND l.INDEX_NAME != r.INDEX_NAME -- but not identical
AND l.INDEX_TYPE = r.INDEX_TYPE
AND CASE
WHEN l.COLUMNS = r.COLUMNS -- we require column equality
AND (l.IS_UNIQUE = 'NO' -- redundant if not unique
OR l.IS_UNIQUE = r.IS_UNIQUE) -- or if same uniqueness as other
THEN TRUE
WHEN l.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(r.COLUMNS, l.COLUMNS) = 1 -- and l is a prefix
AND l.IS_UNIQUE = 'NO' -- redundant if not unique
THEN TRUE
ELSE FALSE
END
GROUP BY l.TABLE_SCHEMA
, l.TABLE_NAME
, REDUNDANT_INDEX_NAME

• RolandoMySQLDBA: I like these. They are quick-and-dirty. They are in my toolbox now. +1 !!!
• surfmuggle: The url linked above is gone. It could be that this url is the one that matches: rpbouman.blogspot.com/2006/09/…