fastest way to count?

[Oziam]

I want the quickest(least resource hungry) way of getting the number of specific rows from a large table. E.g I want to retrieve the number of entries by a specific username.

 

What would be the best method? To use COUNT or just SELECT num_rows?

 

Method 1:

-------------

$query = "SELECT COUNT(usrname) AS usrname FROM table WHERE usrname='$usrname' ";

$res = mysql_query($query) or die(mysql_error());

$array = mysql_fetch_array($res, MYSQL_ASSOC);

$count = $array['usrname'];

 

Method 2:

-------------

$query = "SELECT usrname FROM table WHERE usrname='$usrname' ";

$res = mysql_query($query) or die(mysql_error());

$count = mysql_num_rows($res);

 

Personally I think method 2 would be quicker but I read alot about using count() to speed things up.

 

Thanks!

[Pikachu2000]

Typically, count is faster and more efficient.

[Oziam]

Yeah I agree, I just found more info to back this up;

The method below seems to be the fastest way!

 

$q1 = "SELECT COUNT(id) FROM table WHERE usrname='$usrname' ";

$r1 = mysql_query($q1) or die(mysql_error());

$count= mysql_result($r1,0);

 

mysql_free_result($r1); // only really needed if table has ALOT of entries //

 

Thanks for your reply.