Mysql Optimization - Find Remove Duplicate Indexes

Duplicate index means when we have multiple index in the same column of a table.

Example : duplicate on same column
CREATE TABLE DUPLICATE_INDEX (
  EMAIL varchar(100) NOT NULL,
  PRIMARY KEY (EMAIL),
  KEY EMAIL(EMAIL)
) ENGINE=MyISAM

In this example on column 'EMAIL' we have primary key and index as well and one of them is unnecessary. So ideally we should remove one of index from this table.

Example : duplicate because of composite index
CREATE TABLE DUPLICATE_INDEX (
  EMAIL varchar(100) NOT NULL,
  PHONE varchar(100) NOT NULL,
  UNIQUE KEY (EMAIL,PHONE),
  KEY EMAIL(EMAIL)
) ENGINE=MyISAM

In this example index on column is multiple because of index key EMAIL and UNIQUE key (EMAIL,PHONE) .so key on index EMAIL is irrelevant.

Why we need to remove these duplicate keys
1. They make the optimizer phase slower because MySQL needs to examine more query plans.
2. For every insert / delete / update  , the storage engine needs to maintain, calculate and update more index
3. Disk space will increase and hence the backup time.

How To Find Duplicate Keys
There is a tool in Percona Toolkit that can help you to find all those keys in your schema, its name is pt-duplicate-key-checkerand it can find the  different types of duplicates key


command: pt-duplicate-key-checker --database=test