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

Answer 1

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:

• PDOStatement::rowCount
• MySQL COUNT(*)