Two duplicate indexes with the same columns

Question

I am looking at a table in our database (I didn't make the table), and I see that there are 2 indexes that are exactly the same (I don't know why this was done) just named differently, can this have any negative affect on the table?

Take this example table:

create table mytable(
    mytable_id int unsigned primary key auto_increment,
    user_id int unsigned,
    amount decimal(12,2),
    index user_id_idx(user_id),
    index user_id_2(user_id)
);

Answer 1

Yes, it can have an effect.

Of course the two indexes take extra space on disk and also in memory if they are used.

But they also cause the query optimizer to do more work to calculate the benefit of each index during every SELECT. The more indexes you have, the more cases it has to compare. So it pays off to eliminate truly redundant indexes.

As others have also noted, indexes are updated during INSERT/UPDATE/DELETE operations, so the more indexes you have, the more overhead this represents. Indexes that get a lot of use justify their own overhead, but duplicate indexes take more overhead with no additional benefit to match.

If you're interested, Percona Toolkit has a tool pt-duplicate-key-checker that searches all your tables for cases like this.

Answer 2

Yes, it will impact performance. Every time you write a new row or change the user_id, MySQL has to write three times: once to the table, once to the user_id_idx index, and once to the user_id_2 index. I'd get rid of one of them.

Before dropping the extra index, make sure it's not referenced anywhere. For the most part indexes aren't mentioned by name, but there are exceptions such as index hints.