Is there any harm in resetting the auto-increment?

Question

I have a 100 million rows, and it's getting too big. I see a lot of gaps. (since I delete, add, delete, add.)

I want to fill these gaps with auto-increment. If I do reset it..is there any harM?

If I do this, will it fill the gaps?:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;

Answer 1

Potentially very dangerous, because you can get a number again that is already in use.

What you propose is resetting the sequence to 1 again. It will just produce 1,2,3,4,5,6,7,.. and so on, regardless of these numbers being in a gap or not.

Update: According to Martin's answer, because of the dangers involved, MySQL will not even let you do that. It will reset the counter to at least the current value + 1.

Think again what real problem the existence of gaps causes. Usually it is only an aesthetic issue.

If the number gets too big, switch to a larger data type (bigint should be plenty).

Answer 2

FWIW... According to the MySQL docs applying

ALTER TABLE tbl AUTO_INCREMENT = 1

where tbl contains existing data should have no effect:

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

I ran a small test that confirmed this for a MyISAM table.

So the answers to you questions are: no harm, and no it won't fill the gaps. As other responders have said: a change of data type looks like the least painful choice.

Answer 3

Chances are you wouldn't gain anything from doing this, and you could easily screw up your application by overwriting rows, since you're going to reset the count for the IDs. (In other words, the next time you insert a row, it'll overwrite the row with ID 1, and then 2, etc.) What will you gain from filling the gaps? If the number gets too big, just change it to a larger number (such as BIGINT).

---

Edit: I stand corrected. It won't do anything at all, which supports my point that you should just change the type of the column to a larger integer type. The maximum possible value for a BIGINT is 2^64, which is over 18 quintillion. If you only have 100 million rows at the moment, that should be plenty for the foreseeable future.

Answer 4

I agree with musicfreak... The maximum for an integer (int(10)) is 4,294,967,295 (unsigned ofcoarse). If you need to go even higher, switching to BIGINT brings you up to 18,446,744,073,709,551,615.