0

What's the difference between these two count methods please?

Which one is the best, quickest, ... ?

Thanks.

Method 1:

$query = $pdo->prepare('SELECT COUNT(*) AS count FROM table');
$query->execute();
$fetch = $query->fetch();
$count = $fetch['count'];


Method 2:

$query = $pdo->prepare('SELECT * AS count FROM table');
$query->execute();
$count = $query->rowCount();
F__M
  • 1,518
  • 1
  • 19
  • 34
  • SQL functions is much better I believed. so the first method – Roljhon Apr 08 '17 at 18:27
  • Duplicate: http://stackoverflow.com/questions/19109774/pdorowcount-vs-count – Andrew Larsen Apr 08 '17 at 18:28
  • 2
    The first method is faster because the database doesn't have to select the data, it only has to count it. The second method is better if you need the data AND the number of rows. – Aloso Apr 08 '17 at 18:31
  • Also, you should read how rowCount works (http://php.net/manual/en/pdostatement.rowcount.php) because it will not neccessarily work as you want it to when using select. – Andrew Larsen Apr 08 '17 at 18:31
  • @Aloso the second method is not good in any way unless you are counting rows affected by a insert/update/delete query. – Andrew Larsen Apr 08 '17 at 18:32
  • If you need to retrieve all the data from the table, the second method is the best. In all other cases the first. The first method only needs to transport one number from the database to PHP, whereas the second method (potentially) transports the content of the whole table, although I think the software is designed smart enough not to do that. The best way to find this out is to time it. My guess is that the second method takes longer, but probably not by as much as some expect. Remember, this might depend on the database engine you use. – KIKO Software Apr 08 '17 at 18:38
  • Did you read the documentation? 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. – Andrew Larsen Apr 08 '17 at 18:42

1 Answers1

0

To do only counting, method 1 is much better because the count is resolved by the DBMS and only an integer is returned to the application.

In method 2, a lot of information travels through the network so only in the application the count is made

Guilherme Fidelis
  • 1,022
  • 11
  • 20