SQL COUNT vs mysql_num_rows - what is more efficient?

Databases
1

Greetings,

I am trying to save resources on my web server and was wondering what will be more efficient when it comes to just counting the number of rows:


$query = "SELECT COUNT(*) as num FROM table'";
$total_rows = mysql_fetch_array(mysql_query($query));
$finalcount = $total_rows['num'];

or


$query = "SELECT COUNT(id) as num FROM table'";
$total_rows = mysql_fetch_array(mysql_query($query));
$finalcount = $total_rows['num'];

or


$count = mysql_num_rows(mysql_query("SELECT id FROM table"));

I was searching on Google but kept getting conflicting answers. In all cases, “id” is a primary auto-incrementing key that cannot be null. In the first two SQL Count examples, they are the same except “*” vs “id”.

I thought the “id” would use less resources because “*” selects all the data in the table, but I heard this is not the case though.

The last example is 1 line and selects just “id” and counts.

Please explain which one is most effective in terms of saving web hosting resources (both PHP and MySQL resources) and/or load time. If you know of an even more efficient method, please post it!

Thanks

2

There is not much difference between the first two. They will probably be reduced to much the same request. The third one requires that a recordset be formed of all the ids in the table, true you are not transferring the recordset over the network but on the database server it is more inefficient.

3

actually, you are :slight_smile:

the 3rd query is by far the worst, IF all you want is the count

however, if you’re going to list all rows anyway, as well as the count, then doing an extra query (1st or 2nd) just to get the count is unnecessary overhead

4

Thanks a lot for the help Philip and r937. I will go ahead and use the 1st method then as this sounds like the more efficient one to use.

Kind regards