PDO::rowCount VS COUNT(*)

by

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:

More Related Contents:

  1. PHP / PDO echo data from database into a table
  2. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
  3. How to prevent duplicate usernames when people register?
  4. How to get numeric types from MySQL using PDO?
  5. How can I use PDO to fetch a results array in PHP?
  6. Error when preparing a multiple insert query
  7. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  8. Use bound parameter multiple times
  9. MySQL check if a table exists without throwing an exception
  10. how safe are PDO prepared statements
  11. pdo prepared statements with wildcards
  12. Insert multiple rows with PDO prepared statements
  13. PDO + MySQL and broken UTF-8 encoding
  14. Do SQL connections opened with PDO in PHP have to be closed
  15. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  16. php code to test pdo is available?
  17. PDO fetchAll group key-value pairs into assoc array
  18. What is the advantage of using try {} catch {} versus if {} else {}
  19. Alternative for mysql_num_rows using PDO
  20. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  21. Unknown database error in PHP but it exists in PHPMyAdmin
  22. How do detect that transaction has already been started?
  23. Binding parameters for WHERE IN clause with PDO [duplicate]
  24. return one value from database with mysql php pdo
  25. Trying to access array offset on value of type bool
  26. return multiple response data in one response
  27. How to get a query error from prepare() in PDO PHP?
  28. PHP 7 RC3: How to install missing MySQL PDO
  29. Is a BLOB converted using the current/default charset in MySQL?
  30. Using PDO Prepared Statement and Incrementing a column value

Leave a Comment