[thelist] Auto Increment in database - a goofy question I am sure (long)

[Jay Blanchard]

[snip]
In mysql the definition for an AI index would look like:

	id int not null auto_increment
{/snip]

What I was looking for is a way to reset the auto increment number to start
from a certain point, and I got some very good suggestions/answers. I also
found some information on myisamchk, which is a utility for MySQL, that may
be able to do exactly what is needed.

The AI index (my short-hand for 'auto increment index') is not used as a
foreign key, but occasionaly it is used in queries. The primary function of
the AI index is insure that there is one unique field in the tens of
thousands of records added to each of these tables every day. A lot of these
records are transmitted electronically and come from legacy systems that
look a lot like flat files where the data MAY only have one small bit of
uniqueness. Imagine a business telephone consumer who makes many long
distance calls per day. Each call generates a record...

calltype   date        fromNumber   callDuration toNumber   (plus other
non-unique information)
longDist   2002-4-12   2125551212   01.32        2125551234 (plus other data
in these columns)
longDist   2002-4-12   2125551212   02.40        2125551234 (plus other
data)
carrBill   2002-4-12   2125551212   06.00        2125554321 (plus other
data)

so we get (number of customers X number of calls) = number of records each
day. Having an auto increment field is the only way to insure a unique field
in the record. The record volume is so high that we are nearing the limit
for the auto-increment number. What I need to do in the interim is gain some
of the numbers back by re-ordering (which won't effect the queries) to gain
back the space where some of the records have been deleted. Say I have 6
records who have an AI named 'ID';

1
2
3
4
5
6

Now I for some reason delete the second and forth record, so now the AI
looks like this;

1
3
5
6

If I could restart or reset the index it would go back to (and I would gain
some more room in the index);

1
2
3
4

Which would give me the breathing room to put into place the necessary
maintenance/archiving needed to make the database more efficient.

Thanks all for your answers and suggestions!

Jay