Determining Whether to Resequence a Column
Problem
You have gaps in a sequence column and you’re wondering whether you should try to resequence it.
Solution
Don’t bother. Or at least don’t do so without a good reason, of which there are very few.
Discussion
If you insert records into a table that has an
AUTO_INCREMENT column and never delete any of
them, values in the column form an unbroken sequence. But if you
delete records, the sequence begins to have holes in it. For example,
Junior’s insect table currently
looks something like this, with gaps in the sequence (assuming that
you’ve inserted the cricket and moth records shown
in the preceding section on retrieving sequence values):
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2001-09-10 | kitchen |
| 3 | grasshopper | 2001-09-10 | front yard |
| 4 | stink bug | 2001-09-10 | front yard |
| 5 | cabbage butterfly | 2001-09-10 | garden |
| 6 | ant | 2001-09-10 | back yard |
| 9 | cricket | 2001-09-11 | basement |
| 10 | moth | 2001-09-14 | windowsill |
+----+-------------------+------------+------------+MySQL won’t attempt to eliminate these gaps by
filling in the unused values when you insert new records. People who
don’t like this behavior tend to resequence
AUTO_INCREMENT columns periodically to eliminate the holes. The next few sections show how to do that. It’s also possible to add ...