CODEWITHSUNDEEP
phpmysql
Fredy
Fredy

Reputation: 2910

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

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)?

Upvotes: 5

Views: 3994

Answers (4)

Vikram Jain
Vikram Jain

Reputation: 5588

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(*) .

Upvotes: 0

C-Dog
C-Dog

Reputation: 125

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

Upvotes: 0

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58271

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.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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.

Upvotes: 5

Related Questions