Results 1 to 13 of 13
-
08-29-2006, 03:56 AM #1Web Hosting Evangelist
- 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++;
}
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
-
08-29-2006, 05:07 AM #2Junior Guru Wannabe
- 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++;
}
- Tero
-
08-29-2006, 05:09 AM #3Junior Guru Wannabe
- 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 queriesLast 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.
-
08-29-2006, 05:11 AM #4Web Hosting Evangelist
- 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?
-
08-29-2006, 05:19 AM #5Junior Guru Wannabe
- 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.
-
08-29-2006, 05:20 AM #6Web Hosting Evangelist
- Join Date
- Feb 2006
- Location
- Lancashire, UK
- Posts
- 474
thanks, do i have to give this table a name?
-
08-29-2006, 05:24 AM #7Junior Guru Wannabe
- 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 tweakingAndrew 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.
-
08-29-2006, 05:40 AM #8Web Hosting Evangelist
- 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
-
08-29-2006, 06:06 AM #9Junior Guru Wannabe
- Join Date
- Nov 2004
- Location
- Northamptonshire
- Posts
- 56
Originally Posted by mikey1090Andrew 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.
-
08-30-2006, 05:32 AM #10Aspiring Evangelist
- Join Date
- Mar 2006
- Posts
- 421
Originally Posted by mikey1090
-
08-30-2006, 05:41 AM #11Junior Guru Wannabe
- Join Date
- Nov 2004
- Location
- Northamptonshire
- Posts
- 56
Originally Posted by Ks JeppeAndrew 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.
-
08-30-2006, 07:32 AM #12Web Hosting Evangelist
- 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?
-
08-30-2006, 07:52 AM #13Junior Guru Wannabe
- 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.