$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.
$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.
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?
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?