How to reuse auto_increment values? [duplicate]

Possible Duplicate:
Fragmentation of id's (auto_increment column) in mysql

I have this column in my database. Let's say its name is 'threadid'. It contains unique ids given to each thread for distinction.

threadid 9 8 7 6 5 4 3 2 1

Let's say I have deleted threads with id 5 and 6.

threadid 9 8 7 4 3 2 1

But when there is a submission after the deletion, the unique id given to that thread is 10. not 5. I think this is not neat.

How do I get the least possible value in the column? (in this case, 5.)

I think I can get the minimum value of the column using MIN() and keep +1 until I get the unused, unique value, but I think that's too complicated.

Is there any simple way to do this?

Thanks.

Solution 1:

The maximum number of INT in MySQL 4294967295 if you are creating 1000 threads per minute you would need 1440000 ( 1000 x 60 x 24 ) id's per day. So you would run out of ids after about 8.17 years.

The maximum number of BIGINT is 18446744073709551615 and that would be enough to create 1000000000 (1 billion) ids per minute for 35 096 years.

So you should be fine with just wasting ids as much as you like and not worry about them.

Solution 2:

I agree with the rest of everyone where it is a very bad idea to implement your own find minimal open number. But at where I work, we are given a closed set of number and when a number is free up, we must reuse.

Here is how we did it.

We do not delete the row, but set all values of every column null, So what you do is SELECT min(id) WHERE columnA IS NULL, and if this returns something, we reuse, otherwise, insert a new row.