SQL & PHP - Which is faster mysql_num_rows() or 'select count()'?

I'm just wondering which method is the most effective if I'm literally just wanting to get the number of rows in a table.

$res = mysql_query("SELECT count(*) as `number` FROM `table1`");
$count = mysql_fetch_result($res,0,'number');

or

$res = mysql_query("SELECT `ID` FROM `table1`");
$count = mysql_num_rows($res);

Anyone done any decent testing on this?

Solution 1:

mysql_query() transfers all result records from the MySQL into the php pcrocess before it returns (unlike mysql_unbufferd_query()). That alone would make the mysql_num_rows() version slower.

Furthermore for some engines (like MyISAM) MySQL can serve a Count(*) request from the index of the table without hitting the actual data. A SELECT * FROM foo on the other hand results in a full table scan and MySQL has to read every single dataset.

Solution 2:

Test in database with more then 2300000 rows, type:InnoDB, size near 1 GiB, using xhprof

test1:

    ....SELECT COUNT(id) as cnt FROM $table_name....;
       row= mysqli_fetch_assoc($res2);
   echo $row['cnt'];
        //result1:
        1,144,106
        1,230,576
        1,173,449
        1,163,163
        1,218,992

test2:

....SELECT COUNT(*) as cnt FROM $table_name....;
       row= mysqli_fetch_assoc($res2);
   echo $row['cnt'];
//result2:
1,120,253
1,118,243   
1,118,852
1,092,419
1,081,316

test3:

 ....SELECT * FROM $table_name....;
    echo mysqli_num_rows($res2);
    //result3:
7,212,476
6,530,615
7,014,546
7,169,629
7,295,878

test4:

     ....SELECT * FROM $table_name....;
        echo mysqli_num_rows($res2);
        //result4:
1,441,228
1,671,616
1,483,050
1,446,315
1,647,019

conclusion: The fastest method is in the test2 :

....SELECT COUNT(*) as cnt FROM $table_name....;
       row= mysqli_fetch_assoc($res2);
   echo $row['cnt'];

Solution 3:

Definitely the first. MySQL can usually do this by looking at an index rather than the whole table, and if you use MyISAM (the default), the row count for the table is stored in the table metadata and will be returned instantly.

Your second method will not only read the entire table into memory but also send it to the client through the network before the client counts the rows. Extremely wasteful!