How to reuse auto_increment values? [duplicate]

Question

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.

Answer 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.