Bug #52647 Speed up statement "ALTER TABLE ... AUTO_INCREMENT = ..."
Submitted: 7 Apr 2010 8:50 Modified: 7 Apr 2010 10:27
Reporter: Ralf Ebert Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.1.45 OS:MacOS (10.6.3)
Assigned to: CPU Architecture:Any

[7 Apr 2010 8:50] Ralf Ebert
ALTER TABLE statements that only set the AUTO_INCREMENT counter are somewhat slow and seem to affect the whole table, at least as implied by the mysql console client output:

alter table sometable auto_increment=10000;
Query OK, 25 rows affected (0.13 sec)
Records: 25  Duplicates: 0  Warnings: 0

I'm executing a SQL script that prepares a MySQL database with test data. This truncates all tables and does inserts. As part of that, I need to reset the auto_increment counters of the tables. As it is run many times during the execution of the test suite, it is supposed to run fast. Unfortunately, for ~ 30 tables, the setting of the auto_increment values takes more time (~ 3s) than all the truncate+insert statements (~ 0.5s) 

How to repeat:
- Use 5.1.45 with InnoDB tables
- Execute statements like: alter table sometable auto_increment=10000;
- Observe the query runtime
[7 Apr 2010 10:06] Valeriy Kravchuk
Current limitation of ALTER TABLE are clearly described in the manual (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):

"In other cases, MySQL creates a temporary table, even if the data wouldn't strictly need to be copied."

As altering AUTO_INCREMENT is not listed as a special case when temporary table is not created, statement above applies to it.

So, this is a (useful) feature request at best. Do you agree?
[7 Apr 2010 10:17] Ralf Ebert
Yes, I agree.
[7 Apr 2010 10:27] Valeriy Kravchuk
Actually this is a duplicate of Bug #37927.