PDO::rowCount VS COUNT(*)

Question

i have a query use PDO, count the row first, if row >1 than fetch data

SELECT * WHERE id=:id
$row=$SQL->rowCount();

if($row>0){
    while($data=$SQL->fetch(PDO::FETCH_ASSOC)){...

    }
}
else{echo "no result";}

or

SELECT COUNT(*), * WHERE id=:id
$data=fetch(POD::FETCH_NUM);
$row=data[0];


if($row>0){
//fetch data
}
else{echo "no result";}

Which will be better performance?

2nd. question, if I have set up index on id

which one is better COUNT(id) or COUNT(*)

NOTE: That not all databases will return the row count through `PDO::rowCount` for select statements, tough iirc MySQL does this. — enricog, Oct 01 '13 at 07:06
Your SQL statement will not do what you expect, remove the second star `SELECT COUNT(*) FROM tablename WHERE id=:id`. — martinstoeckli, Oct 01 '13 at 07:34
@martinstoeckli this statement is all right. the only statement in the question though — Your Common Sense, Oct 01 '13 at 07:49
@YourCommonSense - In my understanding `count(*)` would always return 1 (or 0), because the second star always returns a unique row, so it doesn't make sense. But as you correctly pointed out, it works if the question only is whether a row exists or not, i will vote up your answer. — martinstoeckli, Oct 01 '13 at 07:57
@martinstoeckli actually the second star will return a syntax error. While count(*) will return the number of all the rows found — Your Common Sense, Oct 01 '13 at 08:03
@martinstoeckli yet you are right for the case where more than one row have to be returned. — Your Common Sense, Oct 01 '13 at 08:11

Mark · Accepted Answer · 2013-10-30T03:32:41.470

24

1st question:

Using count COUNT(), internally the server(MySQL) will process the request differently.

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

When using $row=$SQL->rowCount(); the server (Apache/PHP) 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.

Take note that PDOStatement::rowCount() returns the number of rows affected by the last statement, not the number of rows returned. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

On my analysis, if you use COUNT(), the process would be divided to both MySQL and PHP while if you use $row=$SQL->rowCount();, the processing would be more for PHP.

Therefore COUNT() in MySQL is faster.

2nd question:

COUNT(*) is better than COUNT(id).

Explanation:

The count(*) function in mysql is optimized to find the count of values. Using wildcard means it does not fetch every row. It only find the count. So use count(*) wherever possible.

Sources:

edited Oct 30 '13 at 03:32

answered Oct 01 '13 at 07:06

Mark

8,046
15
48
78

3

`COUNT(id) is way better than COUNT(*)` it's not. `COUNT(*)` is better – zerkms Oct 01 '13 at 07:07
How about `COUNT(1)` vs `COUNT(*)` and `COUNT(id)`? – eisberg Oct 01 '13 at 07:31
1

@eisberg: Optimizer converts `COUNT(*)` into `COUNT(1)` on a parsing step https://github.com/twitter/mysql/blob/master/sql/sql_yacc.yy#L8946 PS: couldn't find online repo of vanilla mysql, but the parser is not changed in those lines anyway – zerkms Oct 01 '13 at 07:32
2

5 upvotes for the answer which is not a slightly related to a question. Stack Overflow at its best. – Your Common Sense Oct 01 '13 at 08:13
2

downvote: User wants to count fetched rows, not just all rows in the table. count(*) is just wrong. – kworr Oct 01 '13 at 08:28
You seem to have omitted this paragraph about the `rowCount` function: "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, **this behaviour is not guaranteed** for all databases and should not be relied on for portable" applications.` – PLPeeters Oct 02 '13 at 10:51
Still the conclusion "COUNT() in MySQL is faster" is a false statement, as one cannot compare these matters. – Your Common Sense Oct 02 '13 at 14:35

Your Common Sense · Answer 2 · 2017-07-31T04:55:08.817

As a matter of fact, neither PDO rowCount nor COUNT(*) is ever required here.

if row >1 then fetch data

is a faulty statement.
In a sanely designed web-application (I know it sounds like a joke for PHP) one don't have to make it this way.
Most sensible way would be

to fetch first
to use the fetched data

if needed, we can use this very fetched data to see whether anything was returned:

$data = $stmt->fetch();
if($data){
    //use it whatever
} else {
    echo "No record";
}

Easy, straightforward, and no questions like "which useless feature is better" at all.

In your case, assuming id is an unique index, only one row can be returned. Therefore, you don't need while statement at all. Just use the snippet above either to fetch and to tell whether enythin has been fetched.

In case many rows are expected, then just change fetch() to fetchAll() and then use foreach to iterate the returned array:

$data = $stmt->fetchAll();
if($data){
    foreach ($data as $row) {
        //use it whatever
    }
} else {
    echo "No records";
}

Note that you should never select more rows than needed. Means your query on a regular web page should never return more rows than will be displayed.

Speaking of the question itself - it makes no sense. One cannot compare rowCount VS COUNT(*), because it's incomparable matters. These two functions has absolutely different purpose and cannot be interchanged:

COUNT(*) returns one single row with count, and have to be used ONLY if one need the count of records, but no records themselves.
if you need the records, count(whatever) is not faster nor slower - it's pointless.
rowCount() returns the number of already selected rows and therefore you scarcely need it, as it was shown above.

Not to mention that the second example will fetch no rows at all.

Exactly, originating question adds a number of false assumptions: that running different queries can yield the same data, that by not fetching an empty result we are saving something... — kworr, Oct 01 '13 at 08:18
How do you put if($data), if you use while($data=$SQL->fetch(PDO::FETCH_ASSOC))? — Ben, Oct 01 '13 at 08:39
@BenWong judging by the **query** only one row is intended to be returned, as id is supposed to be an unique index. If more than one row is expected, `fetchAll()` have to be used instead. — Your Common Sense, Oct 01 '13 at 08:47

Nikhil · Answer 3 · 2013-10-01T07:12:36.663

1

Performance difference should be negligible to null, since you are issuing only one query in both cases. The 2nd query has to fetch an extra column with the same value for every row matching id, hence it might have a large memory footprint. Even without the COUNT(*) the row count should be available, hence you should go with the 1st solution.

About your 2nd question, AFAIK either COUNT(id) or COUNT(*) will be faster with the index on id, since the db engine will have to perform a range scan to retrieve the rows in question, and range scans are faster with indexes when filtering on the indexed column (in your case id = SOME_ID).

edited Oct 01 '13 at 07:12

answered Oct 01 '13 at 07:00

Nikhil

2,298
13
14

"AFAIK COUNT(id) will be faster with the index" --- `COUNT(col)` will **NEVER** be as fast as `COUNT(*)` – zerkms Oct 01 '13 at 07:07
@zerkms Sorry, I didn't intend to mean it will be faster than COUNT(*), just that it will be faster with the index. Will make the correction – Nikhil Oct 01 '13 at 07:12

Kuzgun · Answer 4 · 2013-10-01T07:11:41.893

Count(id) or count(*) will use index scan so it will be better for performance. Rowcount returns only affected rows and useful on insert/update/delete

EDIT: Since the question edited to compare Count(id) and count(), it makes a slight difference. Count() will return row count affected by select. Count(column) will return non null value count but since it is id, there wont be a null column. So it doesnt make difference for this case.

score 1 · Answer 5 · edited Oct 10 '14 at 21:00

1

Count(*) will be faster.

PDOStatement::rowCount() is not guaranteed to work according to the PDO documentation:

"not guaranteed for all databases and should not be relied on for portable applications."

So in your case I'd suggest using count(*).

See reference: pdostatement.rowcount Manual

edited Oct 10 '14 at 21:00

usm

245
2
17

answered Oct 01 '13 at 07:06

Moeed Farooqui

3,604
1
18
23

To clarify, the `PDOStatement::rowCount()` is not guaranteed and the count you are suggesting is the `Count(*)` function in the select statement? – martinstoeckli Oct 01 '13 at 07:17
1

**Who on the earth upvoting this answer which entirely cintradicts with itself?** – Your Common Sense Oct 01 '13 at 07:31

PDO::rowCount VS COUNT(*)

5 Answers5

Easy, straightforward, and no questions like "which useless feature is better" at all.

Linked

Related