[PHP-WIN] Re: Subject: SQL-query

[Neil Smith]

At 12:10 17/01/2003 +0000, you wrote:

Message-ID: <000a01c2be19$7e0df920$9df81e0a@elstudion>
From: "Bobo Wieland" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Date: Fri, 17 Jan 2003 12:13:35 +0100
MIME-Version: 1.0
Content-Type: text/plain;
        charset="iso-8859-9"
Content-Transfer-Encoding: 7bit
Subject: SQL-query

I hope this isn't to much OT... Sorry if it is...

In my MySQL-db that I use for a php-driven site I have many tables with the
Primary Key set as an auto increment value.

If I have 4 records with id 1, 2, 3 and 4 and then delete number 3 and add
one more record I get the id's 1,2,4 and 5. It isn't much of a problem, but
it would be nice to check for the first "hole" in the id's and insert the
record there instead. How would you do this? is there some simple way from
either MySQl or PHP  that doesn't take to much time to execute?

SQL autoincrement fields don't work like that - if they did, the DBMS would have to scan all the primary key IDs and find a hole to insert it - instead I think it (internally) maintains the last increment ID and adds one to it, which is much faster.

The DBMS in any case will maintain the file structure and occasionally compact the DB file so the 'holes' if they exist are squeezed out. Different DBs do this differently, but you can ask MySQL to compact the table at any time appropriate to your DB - maybe run a timed script to clean the blanks if required.

You may have to live with this, but really, its not a problem at all :-)

HTH
Neil Smith.

thanks in advance


. bobo . www.elstudion.com . [EMAIL PROTECTED] .

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php