> I'm like you, and like to have the numbers sequential, especially for small
> recordsets like that.
>
> What I suggest as quick hack, is to export the data & schema. Drop the
> table. Then create the schema and input the data again. phpMyAdmin is a
> great tool for doing just that.
If you are generating invoice numbers or member id numbers, you
generally don't want to re-use the numbers. Otherwise, some customer
calls in with a complaint about order #00235, and you're not sure which
order #00235 it is. Or you the police ask you about member #00666, and
you're not sure whether it's the current member #00666 or the one last
year.
If you want to re-use records, the general technique is to never
actually delete. You give your record a flag that tells your program a
particular record is not in use any more, and when you need a new record
you search first for records that are not in use.
Even if you just want re-usable numbers for some reason, you're likely
to end up either using the above method or devoting a table to tracking
your numbers in use. The problem is remembering or searching for the
numbers that are not in use.
BTW, a hundred years is approximately 52,595,000 minutes, so even an
ordinary INT requires you to generate 41 new numbers a minute before
you'll run out in a hundred years. If you use a BIGINT and generate, for
instance, 41 new numbers every microsecond, you're not going to run out
for over 7000 years.
Eight bytes is not a lot for an index, so if your auto-increment field
is just being used internally as an index, use a BIGINT and don't worry
about resetting the index.
My 2 yen. (Although I am by no means the first or best person to say it,
right Paul?)
--
Joel Rees
<joel@stripped>
http://www.alpsgiken.co.jp
