How does mysql work with two equal indices?

Question

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?

score 3 · Answer 1 · edited May 23 '17 at 11:55

3

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

edited May 23 '17 at 11:55

Community

1
1

answered Jul 04 '11 at 16:52

Rahul

76,197
13
71
125

SP = Stored Procedure. Those are the stored procedure to find/list out redundant indexex and remove them if you want to. To be employed against the database. Check this link http://malcook-gedanken.blogspot.com/2008_10_01_archive.html – Rahul Jul 04 '11 at 18:05
Right, so these are stored procedures that must be looked up in the "Community Feedback section" and created in your database. This might be worth mentioning in your answer...? – Lightness Races in Orbit Jul 04 '11 at 18:08
@Tomalak, I did mentioned them in my answer. Do you want me to add this link to my answer as well? Will do that then. – Rahul Jul 04 '11 at 18:10
I think it would be useful, yes. You are referring to specific procedures that a third-party has written. – Lightness Races in Orbit Jul 04 '11 at 18:15

score 2 · Answer 2 · answered Jul 04 '11 at 16:32

2

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.

answered Jul 04 '11 at 16:32

Lightness Races in Orbit

378,754
76
643
1,055

Found this in a schema of another project, where I'm not a developer. In the question I mean that mysql not the programmer maintains the index. – giftnuss Jul 04 '11 at 16:42
@giftnuss: Yes, and I used the same meaning of 'maintain'. I think that you should ask the developer why (s)he did this, because it's silly and without purpose (though, ultimately, harmless). – Lightness Races in Orbit Jul 04 '11 at 16:44

score 2 · Answer 3 · answered Jul 04 '11 at 18:36

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.

ypercubeᵀᴹ · Accepted Answer · 2011-07-04T16:58:50.217

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.

How does mysql work with two equal indices?

4 Answers4