Today we were doing some basic scaling exercises on some code, which resulted in several million extra rows in one of our tables in one of the development environments. Rather than refresh the data, we decided just to prune those new records and reset the auto-increment value.
Imagine my surprise when executing the following code hadn’t returned in more than 30 seconds:
alter table sometable auto_increment = 1;
Imagine my further surprise when my query returned the following:
Query OK, 434866 rows affected (4 min 58.41 sec) Records: 434866 Duplicates: 0 Warnings: 0
My only experience with this sort of operation is with renumbering Oracle sequences, so I was initially a bit baffled why resetting this number takes longer than a few milliseconds. I was even more concerned as to why every single row in the table was affected.
It didn’t take me long to figure out what was happening though. It has to do with the way mySQL alters tables. According to the reference manual:
In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed.
One would hope that changing the auto_increment value was one of those few cases where this occur. I re-executed the query and checked the file system and sure enough the temporary files were there:
bash-3.1$ ls *sql* #sql-810_876d.frm #sql-810_876d.ibd