mysql - 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).