MySQL count vs mysql_num_rows()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
stukov
Forum Commoner
Posts: 26
Joined: Sun Jul 24, 2005 2:16 pm
Location: Sherbrooke, Qc, Canada

MySQL count vs mysql_num_rows()

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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().
(#10850)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. ;)
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
Boom.dk
Forum Newbie
Posts: 1
Joined: Mon Mar 05, 2007 1:46 am

Post 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?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

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