PDO :: rowCount VS COUNT(*) [英] PDO::rowCount VS COUNT(*)

问题描述

我有一个使用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()，服务器内部(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)将处理整个结果集，为所有这些结果分配内存，并将服务器置于获取模式，这涉及许多不同的细节，例如锁定.

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.

因此，MySQL中的COUNT()更快.

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

• PDOStatement::rowCount
• MySQL COUNT(*)

这篇关于PDO :: rowCount VS COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！