Determining Whether to Resequence a Column

11.7.1 Problem

You have gaps in a sequence column and you're wondering whether you should try to resequence it.

11.7.2 Solution

Don't bother. Or at least don't do so without a good reason, of which there are very few.

11.7.3 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 a sequence column to a table that doesn't currently have one, force deleted values at the top of a sequence to be reused, and to specify an initial sequence value when creating or resequencing a table.

11.7.4 Reasons to Avoid Resequencing

Before deciding to resequence an AUTO_INCREMENT column, consider whether you really want or need to do so. It's unnecessary in most cases. In fact, renumbering a sequence sometimes can cause you real problems. For example, you should not resequence a column containing values that are referenced by another table. Renumbering the values destroys their correspondence to values in the other table, making it impossible to properly relate records in the two tables to each other.

Reasons that people have for resequencing include the following:

• Aesthetics.

  Sometimes the desire to renumber a column is for aesthetic reasons. People seem to prefer unbroken sequences to sequences with holes in them. If this is why you want to resequence, there's probably not much I can say to convince you otherwise. Nevertheless, it's not a particularly good reason.

• Performance.

  The impetus for resequencing may stem from the notion that doing so "compacts" a sequence column by removing gaps and allows MySQL to run queries more quickly. This is not true. MySQL doesn't care whether or not there are holes, and there is no performance gain to be had by renumbering an AUTO_INCREMENT column. In fact, resequencing affects performance negatively in the sense that the table remains locked while MySQL performs the operationwhich may take a non-trivial amount of time for a large table. Other clients can read from the table while this is happening, but clients that are trying to insert new rows must wait until the operation is complete.

• Running out of numbers.

  The upper limit of a sequence column is determined by the column's data type. If an AUTO_INCREMENT sequence is approaching the upper limit of its column type, renumbering packs the sequence and frees up more values at the top. This may be a legitimate reason to resequence a column, but it is still unnecessary in many cases to do so. You may be able to expand the column's range to increase its upper limit, without changing the values stored in the column.