Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

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
share|improve this answer
    
I like these. They are quick-and-dirty. They are in my toolbox now. +1 !!! –  RolandoMySQLDBA Feb 5 '12 at 3:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.