Feb 22, 2012

Mysql script to get all duplicate indexes in a database

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

Last column also keep the scrip to delete all the duplicates indexes from your database.

No comments:

Post a Comment