Sometimes it is useful to “reset” the numbers of an auto-incrementing field in a MySQL table. This obviously is a VERY bad idea if you have relations to these indices, so make sure you know what you’re doing before executing this on your tables!

So, having warned you, here is the SQL code to do this:

SET @count = 0;
UPDATE IGNORE `table` SET `table`.`key_id` = @count:= @count + 1;

This may not work as you expect if you have a PRIMARY KEY in your table. One way to get around this is to turn the PRIMARY KEY into an INDEX, perform the counter reset and convert the INDEX back to a PRIMARY KEY again:

ALTER TABLE `table` DROP PRIMARY KEY , ADD INDEX ( `key_id` );
SET @count = 0;
UPDATE IGNORE `table` SET `table`.`key_id` = @count:= @count + 1;
ALTER TABLE `table` AUTO_INCREMENT = 1;
ALTER TABLE `table` DROP INDEX `key_id` , ADD PRIMARY KEY ( `key_id` );

If you know of an SQL statement that preserves relations and can resets the counters, then let me know.