MySQL Bugs: #8565: ALTER TABLE MODIFY adds duplicate keys which slow down SELECTs

Bug #8565	ALTER TABLE MODIFY adds duplicate keys which slow down SELECTs
Submitted:	17 Feb 2005 11:50	Modified:	13 Jul 2012 6:58
Reporter:	anzenews asdf	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.30	OS:	Any (*)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello!

I can't believe someone else hasn't filed this yet - but I looked around and couldn't find a bug report. So, here we go:

SQL query ALTER TABLE MODIFY adds duplicate keys instead of replacing the existing ones. This considerably slows down execution of the SELECT queries on the affected tables. 

I believe this is a bug because:
- there should never be duplicate keys on a table (why would someone need them?)
- there shouldn't be any performance penalty on SELECTs if they are duplicated
- when you modify table you request field to be modified - not that the key should be added

I'm sorry I can't test on the latest MySQL - but I believe this is an issue with all 4.x servers (and possibly 3.x too). I have applications running on many servers and they have all been slowing down with time - until now I never could figure out the cause for this. :(

I believe the keys may be added from time to time by other queries as well, but can't really pinpoint it - if I manage to catch another occurrence I will let you know - but it is difficult to trace this.

Thank you!

Regards,

Anze

How to repeat:
mysql> create table t (id int(6) unique);
Query OK, 0 rows affected (0.02 sec)

mysql> show keys from t;  
...
1 row in set (0.00 sec)

mysql> alter table t modify id int(6) unique;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show keys from t; 
...
2 rows in set (0.00 sec)

SELECTs are now slow. If you want to make them faster again:

mysql> ALTER TABLE t DROP INDEX id_2;

Suggested fix:
before adding a key on the table check to see if there exists one that functions in the same way - if it does, don't (ever) add it. There is no reason why there should be two of them.

Hi,

is this bug still existing? I'm doing some mass INSERTs (CSV-Files) INTO MySQL 5.0.22 using prepared statements. Before inserting I ALTER TABLE DISABLE KEYS and afterward ENABLE KEYS. My INDEXES file grows huge. 
When I drop an the index over a DATETIME field and then add the very same index the MYI files grows to an absolutely acceptable size.

Best,
Bernhard

I just see my problem more refers to: http://bugs.mysql.com/bug.php?id=4692

Is anyone working on this? The bug is still there in 5.0.30...

The fix shouldn't be too hard to do and I guess there are many people who don't even realize their queries are slower than they ought to be - because of this flaw. 

Someone? Please?

Best,

Anze

Hi.

This is still on the todo list. Unfortunately there are still bugs with higher priority. I am sorry.

Regards
Ingo

Does that mean that it won't be solved? It's been almost two years since the initial bug report...

Possible, but I can't promise it. ;-(
I have seen a couple of problems that hung around for a long time, and suddenly were raised in priority...

The statement ALTER TABLE MODIFY still adds an index. Even in 6.0.7.

However the select performance doesn't change with one row in the table. How many rows do I need to get a measurable difference?

I have noticed the difference (couldn't miss it actually :) on a table with ~ 5000 rows and 30 indexes on the same column. I know the selects took seconds to complete.
Since then I have used a workaround in my CMS that automatically keeps track of the indexes and removes the unneeded ones, so I can't help you with a more elaborate speed comparison. 

However, duplicate indexes are always bad news - they use up space and they slow down queries (at least updates if not also selects). 

Hope it helps.

Sure, duplicate indexes are bad. This is accepted as being a bug, which has to be fixed eventually.

However it seems that the mere existence of duplicate indexes isn't seen as a big problem.

My guess is that this opinion might change, if we can prove that it has an impact on select performance.

I created the table, inserted 5000 rows, measured SELECT COUNT(*) plus SELECT * ORDER BY id as less than a second, did 29 ALTER TABLE MODIFY, verified that I have 30 indexes, and measured SELECT COUNT(*) plus SELECT * ORDER BY id again as less than a second. So it doesn't seem to be a problem in 6.0.7 any more.

Or do you think, a more complex select is required to show the effect? If so, can you please give an example?

I guess the query should use the index(es), but apart from that I can't really say. The queries I was using were quite simple. 

It is possible that the development of query optimizer minimized the impact of this bug in current MySQL version (the query only uses one index instead of all of them), but it is only a question of time before some changes re-surface this problem. 

You could try doing INSERTs and UPDATEs on a higher number of rows (50000) - updating multiple indexes will take more time, no matter what optimizer does.

Bug#37520 was marked a duplicate of this bug

Closing as a duplicate of Bug#37520