PDO::rowCount VS COUNT(*)

2021-12-20 00:00:00 optimization php mysql pdo

我有一个查询使用 PDO，首先计算行，如果行 >1 则取数据

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";}

或

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


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

哪个性能更好?

第二.问题，如果我在 id 上设置了索引

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

哪个更好 COUNT(id) 或 COUNT(*)

推荐答案

第一个问题:

使用 count COUNT()，服务器(MySQL)在内部会以不同的方式处理请求.

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

在执行COUNT()时，服务器(MySQL)只会分配内存来存储计数的结果.

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

当使用 $row=$SQL->rowCount(); 服务器(Apache/PHP)将处理整个结果集，为所有这些结果分配内存，并将服务器放入fetching mode，涉及到很多不同的细节，比如加锁.

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.

请注意，PDOStatement::rowCount() 返回受最后一条语句影响的行数，而不是返回的行数.如果关联的 PDOStatement 执行的最后一条 SQL 语句是 SELECT 语句，则某些数据库可能会返回该语句返回的行数.但是，这种行为并不能保证适用于所有数据库，并且不应该依赖于可移植应用程序.

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.

根据我的分析，如果您使用COUNT()，则该过程将分为MySQL和PHP，而如果您使用$row=$SQL->rowCount();，PHP 的处理会更多.

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.

因此 COUNT() 在 MySQL 中更快.

Therefore COUNT() in MySQL is faster.

第二个问题:

COUNT(*) 比 COUNT(id) 好.

说明:

mysql 中的 count(*) 函数被优化为查找值的计数.使用通配符意味着它不会获取每一行.它只找到计数.所以尽可能使用 count(*).

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.

来源:

  • PDOStatement::rowCount
  • MySQL COUNT(*)

相关文章