Re: Subject: SQL-query

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

Message-ID: <000a01c2be19$7e0df920$9df81e0a@elstudion> From: "Bobo Wieland" <dev@elstudion.com> To: <php-windows@lists.php.net> 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 . dev@elstudion.com .