Two indexes in one column

Question

I look at the table in our database (I did not make the table), and I see that there are two indexes that are exactly the same (I don’t know why this was done), just named differently, can it have any or negative effect on the table?

Take this sample 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, two indexes take up additional disk space, as well as memory, if used.

But they also make the query optimizer do more work to calculate the benefits of each index during each SELECT. The more indexes you have, the more cases you have to compare. Thus, he expects to eliminate truly redundant indexes.

As others noted, indexes are updated during INSERT / UPDATE / DELETE operations, so the more indexes you have, the more overhead. Indexes that get a lot of benefits justify their own overhead, but duplicate indexes require more overhead without bringing additional benefits to match.

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

Answer 2

Yes, it will affect performance. Each time you write a new line or change user_id , MySQL should write three times: once to the table, once to the user_id_idx index and once to the user_id_2 index. I would get rid of one of them.

Before discarding an additional index, make sure that it is not mentioned anywhere. For the most part, indexes are not mentioned by name, but there are exceptions, such as index hints .