Proper use of COUNT()

Sharif

Can anyone comment on this block of code and tell me if I am using the COUNT() function in mysql wrong or is this the right way:

$sql = "SELECT COUNT(toys) FROM items WHERE owner='45'"
$query = mysql_query($sql);

echo mysql_num_rows($query);

Some people told me it is better to use COUNT() to get number of rows instead of just mysql_num_rows() because it is faster. Is this true, or just plain gossip 😃 ?

LordShryku

It's one less call because you don't need to call mysql_num_rows now, but you have to fetch that row, which if you were just trying to get a count, you wouldn't have to do using mysql_num_rows. So I don't think you'll ever see any significant gain in performance one way or the other. And yes, that looks like the right usage.

nemonoman

Not to be a spoil sport but you're sort of blowing it:

Lets say owner 45 has 20 items

$sql = "SELECT * FROM items WHERE owner='45'"
$query = mysql_query($sql);
echo mysql_num_rows($query);

will echo "20"

$sql = "SELECT COUNT(*) FROM items WHERE owner='45'"
$query = mysql_query($sql);
echo mysql_num_rows($query);

Will echo "1".

Why? because the COUNT function aggregates all the information into a single value in a single row:

$sql = "SELECT COUNT(*) FROM items WHERE owner='45'"
$query = mysql_query($sql);
$row=mysql_fetch_array($result);
echo $row[0];

Will echo "20" -- the number of rows found by the COUNT function

Sharif

nemonoman,

I don't know which post that was a reply to but the original bit of code I posted works fine. I guess it's just pointless because I could just not use the COUNT() function because I end up using mysql_num_rows() anyways.

dartcol

Isn't this quicker?

$sql = "SELECT COUNT(*) FROM table";
$result = mysql_query($sql);
$numrows = mysql_result($result,0);
echo $numrows;