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

Question

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?

Answer 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.