Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003

    Msyql Efficiency Help

    ok...this is is just an example of what I need...don't ask why i would need such a thing b/c its for some script i'm writing for fun...
    a very simple mysql table...
    TABLE urls
    ->FIELD1 INT "id" this is set to primary and autoincrement
    ->FIELD2 VARCHAR "urls"

    what would be the most efficient way to find the next (id) integer in line to be pushed onto the database. Is there an easy way around this w/o doing a major query to the database.

    Remember: the ID is set to autoincrement so it would just be a query to find the max id on that table and just increment.


  2. #2
    Join Date
    Jul 2002

  3. #3
    mysql does it atuomatically on every row inserted. just leave it "null" or blank, it will autoincrement to the next available value.

    say you have just two fields- id and urls.. so you would just do like..

    PHP Code:
    $sql="INSERT INTO table VALUES( '', '$urls')";
    as you see the first value (idI is just a "null" or empty value- mysql will automatically assign teh next available number for 'id' field.

  4. #4
    Join Date
    Jan 2003
    69 guys misunderstood.... i wanted the to find the next Cardinality of that key. w/o inserting anything.

  5. #5
    PHP Code:
    $sql="SELECT MAX(id) FROM table";
    $next=$next['id'];//gives the highest id in the current table
    $next=$next+1;//adds 1
    echo $next//echoes the next id that will be used 

  6. #6
    Join Date
    Jan 2003
    69 are my savior...

    Still wondering if this is the most efficient way... I was looking at LAST_INSERT_ID() but that didn't work well...

  7. #7
    I havent tested this myself but it "might" work...

    PHP Code:
    $sql="SELECT MAX(id) as id+1 FROM table";
    var_dump($next);//dumps the variable entirely, nicluding result type
    echo $next//might work, might not.. untested theory... 

  8. #8
    Join Date
    Apr 2004
    Port St Lucie, FL
    That'll just return an array that looks like this (if PHP will even let it go through):

    'id+1' -> 42 (or whatever the max id is)

    Where you'd want:

    'id' -> 43

    Your first answer will definitely work, though - here's a little efficiency tweaking to it.

    $sql="SELECT MAX(id) FROM table";
    $next=$id_array['id'] + 1; //gives one more than the highest id in the current table
    echo $next; //echoes the next id that will be used
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  9. #9
    Yeah .. suspected as much.. like I said- it was "just theory"..

    I wasnt sure if I could do math in a query using MAX(id) as blah, blah...
    didnt look it up.. never tried it..

  10. #10
    If you don't need to use string indexes into the record array, it is more efficient to use mysql_fetch_row() instead of mysql_fetch_array():

    PHP Code:
    $sql "SELECT MAX(id) FROM table";
    $res mysql_query($sql);
    $id_array mysql_fetch_row($res);
    $next $id_array[0] + 1;
    If you do need string indexes, use mysql_fetch_assoc(). It is generally wisest to use mysql_fetch_array() only when you absolutely must use string and integral indexes. For most applications the performance gains are small, but it is a good habit to get into.

  11. #11

    Re: Msyql Efficiency Help

    Originally posted by section31
    the ID is set to autoincrement so it would just be a query to find the max id on that table and just increment.
    Assuming you allow concurrent access to the table, it won't be that easy; rows may be inserted by other connections while you are calculating the "next" autoincrement ID. You could lock the table - but that's not efficient.

  12. #12
    Join Date
    Jul 2003
    One more point to note, if you delete records, the auto_increment id is not reversed.

    Supposing you have a table with 10 records, and a auto incremented primary key of id.

    Now if you delete row 10, then add another row, the id will be 11, but there will still be 10 rows in the table ... unless you explicitly give the 11th row an id of 10.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  13. #13
    I really don't know if this makes any significant performance difference, but the increment can also be done in SQL as follows:

    PHP Code:
    $sql "SELECT MAX(id) + 1 FROM table"
    $res mysql_query($sql); 
    $id_array mysql_fetch_row($res); 
    $next $id_array[0]; 

  14. #14
    Join Date
    Dec 2002
    NY, NY
    didnt u post that up there ^ ???

  15. #15
    I posted similar code earlier in the thread, yes - but the two copies have minor differences. In the first the addition is done in PHP; in the second the addition is done by the SQL engine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts