Finding Duplicate Indexes with MYSQL

SELECT
    ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,
    CASE
        WHEN ndx1.COLUMNS = ndx2.COLUMNS
        AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
        THEN GREATEST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
        ELSE ndx1.INDEX_NAME
    END REDUNDANT_INDEX_NAME,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN ndx1.COLUMNS = ndx2.COLUMNS
            AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
            THEN LEAST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
            ELSE ndx2.INDEX_NAME
        END
    ) INDEX_NAME
FROM
(
    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',SEQ_IN_INDEX,0), COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx1 INNER JOIN
(
    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'
        , SEQ_IN_INDEX
        , 0)
        , COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx2
ON ndx1.TABLE_SCHEMA = ndx2.TABLE_SCHEMA
AND ndx1.TABLE_NAME = ndx2.TABLE_NAME
AND ndx1.INDEX_NAME != ndx2.INDEX_NAME
AND ndx1.INDEX_TYPE = ndx2.INDEX_TYPE
AND CASE
WHEN ndx1.COLUMNS = ndx2.COLUMNS
AND (ndx1.IS_UNIQUE = 'NO'
OR ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
THEN TRUE
WHEN ndx1.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(ndx2.COLUMNS, ndx1.COLUMNS) = 1
AND ndx1.IS_UNIQUE = 'NO'
THEN TRUE
ELSE FALSE
END
GROUP BY ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,REDUNDANT_INDEX_NAME



Reference url: http://dba.stackexchange.com/questions/18975/how-to-improve-mysql-server-performance