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

Schoolcraft, Jeffrey JSchoolcraft at SytexInc.com
Thu Apr 18 08:47:01 CDT 2002


I'd be interested to hear your suggestions, the only way I've ever done it
was to drop the database (since auto_increment indexes are handled
internally by mysql)

> -----Original Message-----
> From: Jay Blanchard [mailto:jay.blanchard at niicommunications.com]
> Sent: Thursday, April 18, 2002 9:02 AM
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] Auto Increment in database - a goofy
> question I am sure (long)
>
>
> [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
>
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
http://lists.evolt.org Workers of the Web, evolt !



More information about the thelist mailing list