List:General Discussion« Previous MessageNext Message »
From:Joel Rees Date:April 30 2003 5:41am
Subject:Re: Resetting Autoincrement.
View as plain text  
> 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

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

Resetting Autoincrement.Prabu Subroto28 Apr
  • Re: Resetting Autoincrement.Paul DuBois28 Apr
  • Re: Resetting Autoincrement.Gabriel-Dumitrel MOSCALU29 Apr
    • RE: Resetting Autoincrement.Daevid Vincent29 Apr
      • Re: Resetting Autoincrement.Joel Rees30 Apr
    • Re: Resetting Autoincrement.Prabu Subroto29 Apr
Re: Resetting Autoincrement.Prabu Subroto28 Apr
  • re: Re: Resetting Autoincrement.Victoria Reznichenko29 Apr
    • re: Re: Resetting Autoincrement.Prabu Subroto5 May