How does mysql work with two equal indices?

Mysql does not 开发者_StackOverflow社区prevent the creation of an index for the same column(s) when different index names are used. I guess that internally only one index is created and updated, but I better ask here if this is true.

When my suggestion is not right, what is the reason to maintain the index more than one time?

There should be only one index maintained as @Tomalak mentioned. Check this links once

How do MySQL indexes work?

http://www.mysqlperformanceblog.com/2006/08/17/duplicate-indexes-and-redundant-indexes/

ADD:

There could be cases of having redundent indexes on a column. You can find them out by using the SP

I_S_REDUNDANT_INDEXES: lists all redundant indexes

To remove them

I_S_REDUNDANT_INDEXES_DROP 

See this link

http://malcook-gedanken.blogspot.com/2008_10_01_archive.html

• Yes, I'd hope that only one of the two indexes is actually maintained internally. But it doesn't matter, because you shouldn't be doing this.

• There is no reason to maintain two duplicate indexes.

MySQL allows you to create redundant indexes and it maintains them all.

Try mk-duplicate-key-checker which is a nice tool that finds duplicate/redundant indexes. The output has ALTER TABLE statements to drop the duplicates, so you can run it as an SQL script. But I recommend reviewing the output before executing it.

There is also the case when a table has two indexes, one for field-1 and a compound one for (field-1, field-2). The second index makes the first unneeded but I doubt that MySQL does anything less than maintaining both indexes.

It's the developer's or DBA's job to figure than an index is duplicate or redundant and should be removed.

My guess for the reason that such duplicates appeared in the first place is that someone made tests in the past with indexes to optimize some queries and forgot to remove them when done.