CODEWITHSUNDEEP
mysqlindexing
giftnuss
giftnuss

Reputation: 529

How does mysql work with two equal indices?

Mysql does not 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?

Upvotes: 1

Views: 270

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562891

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.

Upvotes: 2

Rahul
Rahul

Reputation: 77926

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

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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.

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385385

  • 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.

Upvotes: 2

Related Questions