Jump to content


Photo

question about mysql count(*)


  • Please log in to reply
3 replies to this topic

#1 dieselmachine

dieselmachine
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 13 May 2006 - 04:50 PM

I am doing some volunteer work on a site that uses a lot of mysql databases, and while looking through the files for code to optimize, i found a script which does this:

(for background, there is a table called users full of user accounts, and each one has a rank. there are 8 different ranks, and the purpose of this script is to tally how many users are of each rank and display the info).

i'm still kind of new to php/mysql, so if my syntax is a bit off, forgive me. It's not the syntax I really care about, just the question of runtime.

SELECT count(*) as total1 FROM users WHERE rank='1'
//send query, get row
$level1users = row['total1'];

SELECT count(*) as total2 FROM users WHERE rank='2'
//send query, get row
$level2users = row['total2'];

... and so on, up to 8. My question is, could I save time by running a single iteration through the table and incrementing 8 different variables based on the value of 'rank'? It seems to me this current script is going through the entire table 8 different times, which (in my mind) makes me think it could be optimized.

I'm thinking:
SELECT rank FROM users;
//send query
while{
array[$row['rank']]++;
}

then at the end, print the 8 variables in the array. Would this be more efficient?

Thanks in advance for any guidance.

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 May 2006 - 05:04 PM

(for background, there is a table called users full of user accounts, and each one has a rank. there are 8 different ranks, and the purpose of this script is to tally how many users are of each rank and display the info).

You want to use [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-functions-and-modifiers.html\" target=\"_blank\"]GROUP BY[/a].
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] rank, COUNT(*) AS total FROM users GROUP BY rank
[!--sql2--][/div][!--sql3--]

#3 ignace

ignace
  • Moderators
  • Now mod flavored
  • 6,431 posts
  • LocationBelgium

Posted 13 May 2006 - 05:25 PM

function getRank( $start, $stop ){
global $dbResource; // <- output from mysql_connect( bla... );
$arr = array();
for( $curRank = $start; $start<$stop; $curRank++ ){
$query = "SELECT count( `rank` ) as `userRank` FROM `users` WHERE rank='.$curRank.'";
$resource = mysql_query( $query, $dbResource );
while( false !== ( $userRank = mysql_fetch_array( $resource ))){
$arr[$curRank] = $userRank;
}
}
asort( $arr );
return $arr;
}


$rankings = getRank( 0, 8 );
if( is_array( $rankings )){
for( $i=0; $i < count( $rankings ); $i++ ){
print('
...data.comes.here...'.$rankings[$i].'...and.so.on...
');
}
}

#4 dieselmachine

dieselmachine
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 May 2006 - 09:13 PM

[!--quoteo(post=373539:date=May 13 2006, 10:04 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 13 2006, 10:04 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
You want to use [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-functions-and-modifiers.html\" target=\"_blank\"]GROUP BY[/a].
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] rank, COUNT(*) AS total FROM users GROUP BY rank [!--sql2--][/div][!--sql3--]
[/quote]

You are totally awesome, thanks a ton dude!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users