I've got a 100 million rows, and it is getting too large. I see lots of gaps. (since i have remove, add, remove, add.)

I wish to fill these gaps with auto-increment. Basically do totally reset it..can there be any harM?

Basically do that, does it fill the gaps?:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;

Potentially very harmful, because you can aquire a number again that's already being used.

That which you propose is resetting the succession to at least one again. It'll just produce 1,2,3,4,5,6,7,.. and so forth, no matter these amounts finding yourself in a gap or otherwise.

Update: Based on Martin's answer, due to the risks involved, MySQL won't even let you accomplish that. It'll totally reset the counter to a minimum of the present value + 1.

Reconsider what real problem the presence of gaps causes. Usually it is simply a pleasing problem.

When the number will get too large, change to a bigger data type (bigint ought to be plenty).

FWIW... Based on the MySQL docs using

ALTER TABLE tbl AUTO_INCREMENT = 1

where tbl consists of existing data shouldn't have any effect:

To alter the need for the AUTO_INCREMENT counter for use for new rows, do that:

ALTER TABLE t2 AUTO_INCREMENT = value

You can't totally reset the counter to some value under or comparable to any that happen to be used. For MyISAM, if the worthiness is under or comparable to the maximum value presently within the AUTO_INCREMENT column, the worthiness is totally reset to the present maximum plus one. For InnoDB, when the value is under the present maximum value within the column, no error happens and also the current sequence value isn't transformed.

I went a little test that confirmed this for any MyISAM table.

Therefore the solutions for you questions are: no harm, with no it will not fill the gaps. As other responders have stated: a big change of information type appears like minimal painful choice.

Odds are you would not gain everything from carrying this out, and also you could easily ruin the application by overwriting rows, since you are likely to totally reset the count for that IDs. (Quite simply, next time you place a row, it'll overwrite the row with ID 1, after which 2, etc.) What's going to you will get from filling the gaps? When the number will get too large, just change it out to some larger number (for example BIGINT).


Edit: I stand remedied. It will not do anything whatsoever whatsoever, which assists my point that you ought to just change the kind of the column to some bigger integer type. The utmost possible value for any BIGINT is 2^64, that is over 18 quintillion. Should you have only 100 million rows right now, that needs to be plenty for that expected future.

To be sure with musicfreak... The utmost to have an integer (int(10)) is 4,294,967,295 (unsigned ofcoarse). If you want to go even greater, switching to BIGINT brings you as much as 18,446,744,073,709,551,615.