Page 1 of 1
Optimize
Posted: Sun Apr 16, 2006 4:30 am
by Terriator
I have following:
Code: Select all
$result_this = doquery("SELECT * FROM users WHERE village = '$user[village]'");
$academy = 0;
$genin = 0;
$chuunin = 0;
$jounin = 0;
$s_jounin = 0;
while($r=mysql_fetch_array($result_this)){
if($r[rankID]==1){$academy = $academy + 1;}
if($r[rankID]==2){$genin = $genin + 1;}
if($r[rankID]==3){$chuunin = $chuunin + 1;}
if($r[rankID]==4){$jounin = $jounin + 1;}
if($r[rankID]==5){$s_jounin = $s_jounin + 1;}
}
Which is kinda slow when it's going through thousands of rows. How do I optimize this?
Posted: Sun Apr 16, 2006 4:57 am
by Oren
Dang I see this again and again... Why do you use all these if's?!? Why do you need to check all the cases after one case has already matched?
Use if and elseif control structures instead. If you want it to be even faster then use the
switch structure.
I've read that it is a little bit faster than if and elseif control structures (most of the time).
Posted: Sun Apr 16, 2006 5:15 am
by Terriator
Yea okay, lol, this code was written half a year ago when i started coding.. Anyways, just changed it to switch structure, but it's still rather slow - And takes a good amount of server resources, I think. So is there a faster way than to make it loop through all rows? All I need it to do is count the amount of each rank...
Posted: Sun Apr 16, 2006 5:19 am
by Benjamin
Code: Select all
SELECT `rankID` FROM `users` WHERE `village` = '$user[village]'
Make sure the village field is indexed as well in the database table.
Posted: Sun Apr 16, 2006 5:22 am
by Oren
If that's what you want to do you can try to do it like this:
Code: Select all
$result_this = doquery("SELECT rankID FROM users WHERE village = '$user[village]' AND rankID = 1");
$academy = mysql_num_rows($result_this);
And then do the same with each one of the other ranks.
Note: this is just a suggestion and I really don't know whether it's faster or not! it might be even slower so just try doing it like this and to see if it is faster. If it is - cool, use it! If it isn't, then don't use it

Posted: Sun Apr 16, 2006 9:10 am
by Ambush Commander
If you've got thousands of rows, I might suggest adding some redundancy and creating a `village` table that has the counts for each of the types of ninja.
Re: Optimize
Posted: Sun Apr 16, 2006 9:20 am
by timvw
Leave the counting to your dbms... Here's an example that should get you started:
Code: Select all
SELECT COUNT(*) FROM users GROUP BY rankID