Web Development Blog

MySQL COUNT(*) VS PHP mysql_num_rows

 Here is the question of the day: What is faster way to get total number of records in the table? Answer is COUNT(*), is much faster, below you will find the test that we performed and detailed result of the test.

So we ran 2 scripts 1000 times each with the table that have 90,000 records, and measured average execution time for each script

Script 1 - PHP mysql_num_rows:  0.02186003613472 seconds

                $sSQL="SELECT 1 FROM table";

                $result=mysql_query($sSQL) or die ("MySQL err: ".mysql_error()."<br />".$sSQL);

                $mysql_num_rows=mysql_num_rows($result);

 

Script 2 - MySQL COUNT(*):  0.00014628839492798 seconds

 

                $sSQL="SELECT COUNT(1) AS 'count' FROM table";

                $result=mysql_query($sSQL) or die ("MySQL err: ".mysql_error()."<br />".$sSQL);

                if ($row = mysql_fetch_array($result)){

                                $count=$row["count"];

                }

So, MySQL COUNT(*) is 149.43 times faster.

Associated tags:  PHP, MySQL, COUNT, Mysql_num_rows, Mysql_query, Mysql_fetch_array

Add Comment:

CAPTCHA