MySQL Bugs: #52647: Speed up statement "ALTER TABLE ... AUTO

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:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.45	OS:	MacOS (10.6.3)
Assigned to:		CPU Architecture:	Any

Description:
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

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?

Yes, I agree.

Actually this is a duplicate of Bug #37927.