How to identify duplicate indexes
I am an experienced Oracle DBA, but new (one month) to MySQL, and I have been tasked with identifying duplicate indexes in our database. Here is the structure from one table:
PRIMARY KEY (`accountNum`),
UNIQUE KEY `acceptedProgramId` (`acceptedProgramId`),
UNIQUE KEY `mgageId` (`mgageId`),
UNIQUE KEY `autoId` (`autoId`),
UNIQUE KEY `helocId` (`helocId`),
UNIQUE KEY `hePreRefiId` (`hePreRefiId`),
UNIQUE KEY `origAcctPort` (`originalAccountNumber`,`portfolioId`),
UNIQUE KEY `ctAccountId` (`ctAccountId`),
UNIQUE KEY `accountDetailId` (`accountDetailId`),
KEY `FK1D0C220DB6EE652` (`agentId`),
KEY `FK1D0C220D250E40CC` (`agencyQueueId`),
KEY `FK1D0C220D6F9D1D1B` (`helocId`),
KEY `FK1D0C220D7062C296` (`claimAgentId`),
KEY `FK1D0C220DF5C3DF5F` (`defaultResponsibleId`),
KEY `FK1D0C220DCBD03E64` (`portfolioId`),
KEY `FK1D0C220D799222BA` (`agentQueueId`),
KEY `FK1D0C220DA2B8AD17` (`teamQueueId`),
KEY `FK1D0C220D8C14235C` (`divisionId`),
KEY `FK1D0C220D21D446FF` (`autoId`),
KEY `FK1D0C220D777A2B5D` (`acceptedProgramId`),
KEY `FK1D0C220DCDB05A99` (`mgageId`),
KEY `FK1D0C220D164CC611` (`hePreRefiId`),
KEY `FK1D0C220D916BE7D8` (`ctAccountId`),
KEY `FK1D0C220D15472F70` (`accountDetailId`)
If you look closely, you will notice that the following pairs of indexes use the same column; except that one uses the UNIQUE constraint, they are identical:
UNIQUE KEY `acceptedProgramId` (`acceptedProgramId`) = KEY `FK1D0C220D777A2B5D` (`acceptedProgramId`)
UNIQUE KEY `mortgageId` (`mgageId`) = KEY `FK1D0C220DCDB05A99` (`mgageId`)
UNIQUE KEY `autoId` (`autoId`) = KEY `FK1D0C220D21D446FF` (`autoId`)
UNIQUE KEY `helocId` (`helocId`) = KEY `FK1D0C220D6F9D1D1B` (`helocId`)
UNIQUE KEY `hePreRefiId` (`hePreRefiId`) = KEY `FK1D0C220D164CC611` (`hePreRefiId`)
UNIQUE KEY `citiAccountId` (`ctAccountId`) = KEY `FK1D0C220D916BE7D8` (`ctAccountId`)
UNIQUE KEY `accountDetailId` (`accountDetailId`) = KEY `FK1D0C220D15472F70` (`accountDetailId`)
That was fairly easy to do on this one table, but the database has 150 tables, and I want to write a script of some type to identify the duplicate pairs (triplets, etc.) across the entire database. I have tried several times to write something myself that uses the information in the KEY_COLUMN_USAGE and COLUMNS tables, but I cannot seem to get what I need.
Anybody have something that would work for me? Even something that would just list all indexes for all tables would be a good start; somehow I can't even seem to get that correctly from INFORMATION_SCHEMA. I can figure out how to identify the duplicates from there.