Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    474

    need help omtimizing a php mysql while loop

    Ok, i have a database that contains players details, scores etc for my game.

    Each 10 seconds, in my "tick" file, i run this command to give each player a rank, based on their score.


    PHP Code:
    $result mysql_query("SELECT id FROM game ORDER BY score DESC");
    $rank=1;

    while (
    $myrow=mysql_fetch_array($result)) {

    $id $myrow["id"];
    $result=mysql_query("UPDATE game SET rank=$rank WHERE id=$id");

    $rank++;


    problem is, it takes 1 second to execute....

    when i remove that code, the rest of the file takes 0.02 seconds to execute.

    I only have 520 entries in that table, but with around 20 people registering per day, things are not going to get an better.

    The reason for this post, is because im upgrading my game with a new version.

    One of the things i wish to upgrade is optimizing mysql queries, therefore allowing my game to support more users.

    So, what can i do to optimize that query, and other queries like it.

    I have set score as an index, and id is a primiary key, so that will allow mysql to shoot through it faster.



    mike

  2. #2
    Join Date
    Nov 2002
    Location
    Finland
    Posts
    96
    Hi,


    Try this....


    PHP Code:
    $result mysql_query("SELECT id FROM game ORDER BY score DESC");
    $rank=1;

    while (
    $myrow=mysql_fetch_row($result)) {

    $id $myrow[0];
    mysql_query("UPDATE game SET rank=$rank WHERE id=$id");

    $rank++;


    Pulling an associative array from db is always much slower than fetcing a normal array...



    - Tero

  3. #3
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    You could do it by creating a tempory table with rank as an auto_increment. Then you could do:

    INSERT INTO temp_game SET id = (SELECT id FROM game ORDER BY score DESC);

    Rank will automatically be counted in.

    You could then do:

    UPDATE game, temp_game SET game.rank = temp_game.rank WHERE game.id = temp_game.id

    And that should drop it down to 2 queries
    Last edited by A-Wing; 08-29-2006 at 05:12 AM.
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

  4. #4
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    474
    when you say tempory table, will that just be created by the php script, or will it be permanent in the database?

  5. #5
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    It will be created by the PHP script, and deleted automatically when the connection closes.
    You just have to run a query: "CREATE TEMPORARY TABLE temp_game (id INT NOT NULL AUTO_INCREMENT, rank INT NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM"
    Last edited by A-Wing; 08-29-2006 at 05:24 AM.
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

  6. #6
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    474
    thanks, do i have to give this table a name?

  7. #7
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    Give it any unused table name, I just used temp_game to make it easy to identify.

    I haven't tested those queries, they are pure theory from the top of my head so they may need tweaking
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

  8. #8
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    474
    will the mysql_close remove the table?

    oh, and

    $id = $myrow[0];

    that theory did not work, the script executed the same time as it was previously

  9. #9
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    Quote Originally Posted by mikey1090
    will the mysql_close remove the table?
    Yes it will
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

  10. #10
    Join Date
    Mar 2006
    Posts
    421
    Quote Originally Posted by mikey1090
    will the mysql_close remove the table?

    oh, and

    $id = $myrow[0];

    that theory did not work, the script executed the same time as it was previously
    Did you remember to change from mysql_fetch_array to mysql_fetch_row?

  11. #11
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    Quote Originally Posted by Ks Jeppe
    Did you remember to change from mysql_fetch_array to mysql_fetch_row?
    That shouldn't make much difference, maybe about 10th of a second faster. The slow bit is running multiple update queries. The way to optimise this task is to reduce the number of queries being fired out.
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

  12. #12
    Join Date
    Feb 2006
    Location
    Lancashire, UK
    Posts
    474
    hi,

    i just tested the code with the tempory table...it was bad.

    it took 10 and 12 seconds to execute.


    any ideas now?

  13. #13
    Join Date
    Nov 2004
    Location
    Northamptonshire
    Posts
    56
    That's odd, you are using MySQL 4.1 aren't you? In 3.23 and possibly 4.0 the queries I gave you will be very slow due to the way they are handled.
    Try changing the engine type to memory I guess, although the server should be doing this anyway with such a small temporary table.

    I'm out of ideas, in general you need to reduce the query count, and probably optimise your SQL server as 500 odd SQL update queries really shouldn't be that slow. The mysqli commands in PHP should be faster too (but again that depends on MySQL 4.1).
    Andrew Hutchings (A-Wing) - Linux Jedi
    A-Wing Internet Services
    Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you.

Posting Permissions

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