As we know indexes are good but
over indexes are bad for DML statements. If there are multiple people are
working are a same project there good probability of duplicate indexes. So first
of all I would like to explain what is meaning of duplicate indexes.
Meaning
of duplicate index:
Consider on following two indexes:
1. CREATE INDEX NCI_One(a,b);
2. CREATE INDEX NCI_Two(b,a);
Both indexes are not duplicate
indexes because in an index order of column matters. So both indexes NCI_One and NCI_Two are totally different indexes not duplicate
indexes.
Consider on following four indexes:
1. CREATE INDEX NCI_One(a);
2. CREATE INDEX NCI_Two(a,b);
3. CREATE INDEX NCI_Three(a,b,c);
4. CREATE INDEX NCI_Four(b,c);
In the above example, NCI_One and
NCI_Two are two duplicate indexes since it is subset of index NCI_Three. NCI_Four
is also subset of index NCI_Three but it is not a duplicate index since it is not
subset from first column.
Mysql
script to get or list out all the duplicate indexes in database:
SELECT
tblAllIndex.TABLE_SCHEMA AS DatabaseName,
tblAllIndex.TABLE_NAME AS TableIndex,
tblAllIndex.INDEX_NAME AS IndexName,
tblDuplicateIndex.INDEX_NAME AS DuplicateIndexName,
CONCAT('DROP INDEX `',tblDuplicateIndex.INDEX_NAME,'` ON `',tblDuplicateIndex.TABLE_SCHEMA,'`.`',
tblDuplicateIndex.TABLE_NAME,'`;') AS DropDuplicateIndexScript
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) KeyList
FROM information_schema.STATISTICS
GROUP BY
TABLE_SCHEMA,TABLE_NAME,INDEX_NAME
)
tblAllIndex
INNER JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) KeyList
FROM information_schema.STATISTICS
GROUP BY
TABLE_SCHEMA,TABLE_NAME,INDEX_NAME
)
tblDuplicateIndex
ON
tblAllIndex.TABLE_SCHEMA = tblDuplicateIndex.TABLE_SCHEMA
AND
tblAllIndex.TABLE_NAME =
tblDuplicateIndex.TABLE_NAME
AND
tblAllIndex.INDEX_NAME <>
tblDuplicateIndex.INDEX_NAME
WHERE tblDuplicateIndex.KeyList
= LEFT(tblAllIndex.KeyList,LENGTH(tblDuplicateIndex.KeyList))
ORDER BY tblAllIndex.TABLE_SCHEMA,tblAllIndex.TABLE_NAME