Reset AUTO_INCREMENT Value on Large Tables

MySQL Tutorials - Herong's Tutorial Examples

∟Reset AUTO_INCREMENT Value on Large Tables

This section provides a tutorial example on how to reset the AUTO_INCREMENT value by inserting a temporary record with a given value on the AUTO_INCREMENT column.

Have you ever tried to reset the AUTO_INCREMENT value on a large table? It changes a single value in the table definition and should be very fast on any table.

However, when I ran the following command on a MyISAM table with about 80,000,000 records, it continued for 30 minutes:

ALTER TABLE Posts AUTO_INCREMENT=90000001;

By looking at the data directory, I saw that the MyISAM engine started to rebuild the table with temporary data and index files.

-rw-r-----. 1 mysql mysql 10374660776 Apr 1 08:44 Files.MYD
-rw-r-----. 1 mysql mysql  4851155968 Apr 1 08:44 Files.MYI
...
-rw-r-----. 1 mysql mysql  9661212268 Apr 1 08:57 #sql-1fd7f7_8.MYD
-rw-r-----. 1 mysql mysql  1182448640 Apr 1 09:13 #sql-1fd7f7_8.MYI

Instead of waiting the command to finish, I killed it and used the following workaround, which takes less than a second.

INSERT INTO Posts (ID, ...) VALUES (90000000, ...);
DELETE FROM Posts WHERE ID = 90000000;

Note that the ID column is defined as AUTO_INCREMENT. When the ID column is specified with a value higher than the current AUTO_INCREMENT value, MyISAM engine will automatically update the AUTO_INCREMENT to the next value after the specified value.