Which MySQL query is effective to get the total number of records

Question

To get the total number of records, I usually use this query:

$total= mysql_num_rows(mysql_query("SELECT id FROM t_statistic WHERE pageid = $pid"));

but I got one the other query like below:

$data = mysql_fetch_object(mysql_query("SELECT COUNT(id) AS num_rows FROM t_statistic WHERE pageid = $pid"));
$total = $data->num_rows;

Between the two queries above. Which is more quickly and effectively (when the total number of records in the millions)?

Answer 1

I prefer the second query. It gives you already the record count, while the first query gives you the list of IDs (not the count), although it has been filtered but there are some cases when ID exist more than once in the table.

Answer 2

The Second query is quick and efficient:

SELECT COUNT(id) AS num_rows FROM t_statistic WHERE pageid = $pid

If you know about query optimisation. The query will only keeps only count in memory while calculating the answer. And directly gives number of rows.

Where as first query:

SELECT id FROM t_statistic WHERE pageid = $pid

Keeps all the selected rows in memory. then number of rows are calculated in further operation.

So second query is best in both ways.