CODEWITHSUNDEEP
mysql
javsmo
javsmo

Reputation: 1337

What's the reason of duplicate unique index in MySQL

I got the task of adapt a very old MySQL database to use its data on another program and I found some very large tables (thousands of rows) with this kind of structure:

CREATE TABLE `foo` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Name` varchar(200) NOT NULL,
    PRIMARY KEY (`Id`),
    UNIQUE KEY `foo_Name_Idx` (`Name`),
    UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

What's the reason of these apparently duplicate unique index? Perfomance? Automated table creation error?

Upvotes: 2

Views: 98

Answers (1)

Ike Walker
Ike Walker

Reputation: 65547

There is no benefit to redundant indexes like that. MySQL allows you to create them if you specify different names for them, but they should be avoided.

If you want to find all of the redundant indexes in your database I recommend that you use one of these tools:

Once installed you can run it at the command line like this:

pt-duplicate-key-checker

Once installed you can query it like this:

SELECT * FROM common_schema.redundant_keys\G

Upvotes: 2

Related Questions