SELECT COUNT() vs mysql_num_rows();

Solution 1:

Use COUNT, internally the server will process the request differently.

When doing COUNT, the server will only allocate memory to store the result of the count.

When using mysql_num_rows, the server will process the entire result set, allocate memory for all those results, and put the server in fetching mode, which involves a lot of different details, such as locking.

Think of it like the following pseudo scenarios:

SELECT COUNT(*)

Hey Bob, how many people are in the class room?

mysql_num_rows

Hey Bob, send all the people from the classroom over to me, ... I'll count them to get the number of people myself

In summary, when using mysql_num_rows you are transferring all records to the client, and the client will have to calculate the count itself.

Solution 2:

Use COUNT(id). It only returns the count, With mysql_num_rows($result); php fetch ALL the data from the mysql and count the number of found results.

And finally, don't use mysql_* functions.

Suggested alternatives

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_stmt_num_rows() PDOStatement::rowCount()

Solution 3:

Tested in inoDB engine and mysql 5.5.

The id has index and I think this is very fast

$q = "SELECT count(`id`) FROM table where 1";
$rows = mysql_query($q);
$count = mysql_fetch_array($rows);
echo $count[0];

if you want more, you have to use one index just on id or what ever you want to select.

Caching is another solution and you can select from 1 set of records in few milliseconds!