March 1, 2008

Resetting auto_increment in MySQL

Filed under: MySQL — Tags: , — Gregory Haase @ 11:01 pm

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*

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

%d bloggers like this: