Optimize

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Terriator
Forum Commoner
Posts: 60
Joined: Mon Jul 04, 2005 12:46 pm

Optimize

Post 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?
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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).
Terriator
Forum Commoner
Posts: 60
Joined: Mon Jul 04, 2005 12:46 pm

Post 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...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 :wink:
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Optimize

Post 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 
Post Reply