Page 1 of 1

MySQL count vs mysql_num_rows()

Posted: Tue Jan 30, 2007 9:41 pm
by stukov
Good day,

What is the faster way to count the rows in a table (MySQL db): a count(`column`) or SELECT * then mysql_num_rows?

Thanks.

Posted: Tue Jan 30, 2007 10:04 pm
by Christopher
mysql_num_rows() is faster because you don't need to fetch the row to get the data like you do with COUNT().

Posted: Tue Jan 30, 2007 10:17 pm
by feyd
If you just want to find out the number of records in the table, COUNT will be faster most often.

You could also look at the status data for the table.. ;)

Posted: Tue Jan 30, 2007 10:32 pm
by AKA Panama Jack
arborint wrote:mysql_num_rows() is faster because you don't need to fetch the row to get the data like you do with COUNT().
Actually that's not really true at all. :)

Code: Select all

$resultId = mysql_query( "SELECT id FROM mytable WHERE cat_id='200'" );
$numOfRows = mysql_num_rows( $resultId );
If you use mysql_num_rows() you have to execute a query that returns all of the data even if you have select only one field. If the table has 1 million records and you have selected one INT field the query will be returning a result set of 1 million rows. The mysql_num_rows() will count all of those rows. So you have the large overhead of the initial query even though the mysql_num_rows() is fast. You end up losing out on speed and memory by doing it this way.

Code: Select all

$resultId = mysql_query( "SELECT COUNT(id) as total FROM mytable WHERE cat_id='200'" );
$resultset = mysql_fetch_array($resultId);
$numOfRows = $resultset['total'];
If you use the MySql COUNT then it will only be returning ONE row and the COUNT will work off the indexes and be considerably faster.

The second example is considerably faster and uses far less database memory and processor time.

Posted: Mon Mar 05, 2007 1:53 am
by Boom.dk
I'm sorry to bump this old topic, but I have question.

If you need both the data and the row count, wouldn't the first example be best? And the second example would be best if you only needed the row count, right?

Posted: Mon Mar 05, 2007 2:18 am
by AKA Panama Jack
Boom.dk wrote:I'm sorry to bump this old topic, but I have question.

If you need both the data and the row count, wouldn't the first example be best? And the second example would be best if you only needed the row count, right?
Right on both counts.