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)?
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.
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.
Definitely the second one.
Some engines, like MySQL can do a count just by looking at an index rather than the table's data.
I've used something like the following on databases with millions of records.
SELECT count(*) as `number` FROM `table1`;
Way faster than: mysql_num_rows($res);
BTW: The * in Count(*) basically means it won't look at the data, it will just count the records, as opposed to Count(colname).
1) SELECT COUNT(*) FROM t_statistic WHERE pageid = $pid" --> count(*) counts all rows
2)SELECT COUNT(id) FROM t_statistic WHERE pageid = $pid" --> COUNT(column) counts non-NULLs only
3) SELECT COUNT(1) FROM t_statistic WHERE pageid = $pid" -->COUNT(1) is the same as COUNT(*) because 1 is a non-null expressions
Your use of COUNT(*) or COUNT(column) should be based on the desired output only.
So. Finally we have result is count(column) is more faster compare to count(*) .
