Generating and Using Sequences - MySQL - page 458

| 10 | moth | 2014-09-14 | windowsill |
+----+-------------------+------------+------------+
MySQL won't attempt to eliminate these gaps by filling in the unused values when you
insert new rows. People who dislike this behavior tend to resequence AUTO_INCRE
MENT columns periodically to eliminate the holes. The examples in this section show
how to do that. It's also possible to extend the range of an existing sequence (see
Recipe 13.6 ), force deleted values at the top of a sequence to be reused (see
Recipe 13.7 ), number rows in a particular order (see Recipe 13.8 ), or add a sequence
column to a table that doesn't currently have one (see Recipe 13.9 ).
Before you decide to resequence an AUTO_INCREMENT column, consider whether that's
really necessary. It usually isn't, and in some cases 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 rows in the two tables to each other.
Here are reasons I have seen advanced for resequencing a column:
Aesthetics
Some people 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 “com‐
pacts” a sequence column by removing gaps and enables MySQL to run statements
more quickly. This is not true. MySQL doesn't care whether there are holes, and
there is no performance gain to be had by renumbering an AUTO_INCREMENT col‐
umn. In fact, resequencing affects performance negatively in the sense that the table
remains locked while MySQL performs the operation—which may take a nontrivial
amount of time for a large table. Other clients can read from the table while this is
happening, but clients trying to insert new rows block until the operation is com‐
plete.
Running out of numbers
The sequence column's data type and signedness determine its upper limit (see
Recipe 13.2 ). If an AUTO_INCREMENT sequence is approaching the upper limit of its
data 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. You may be able to change the column data type to increase its upper
limit without changing the values stored in the column; see Recipe 13.6 .
If you're still determined to resequence a column, it's easy to do: drop the column from
the table; then put it back. MySQL renumbers the values in the column in unbroken