php PDO::rowCount VS COUNT(*)

PDO::rowCount VS COUNT(*)

提问by Ben

i have a query use PDO, count the row first, if row >1 than fetch data

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

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

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

which one is better COUNT(id)or COUNT(*)

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

回答by Christian Mark

1st question:

第一个问题：

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

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

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

这样做时COUNT()，服务器（MySQL）只会分配内存来存储计数结果。

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.

使用$row=$SQL->rowCount();服务器时（Apache/PHP）将处理整个结果集，为所有这些结果分配内存，并将服务器置于获取模式，这涉及许多不同的细节，例如锁定。

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 PDOStatementwas a SELECTstatement, 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.

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

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 和 PHP，而如果使用$row=$SQL->rowCount();，则处理会更多地用于 PHP。

Therefore COUNT()in MySQL is faster.

因此COUNT()在 MySQL 中速度更快。

2nd question:

第二个问题：

COUNT(*)is better than COUNT(id).

COUNT(*)比 好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.

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

Sources:

资料来源：

• PDOStatement::rowCount
• MySQL COUNT(*)

• PDOStatement::rowCount
• MySQL 计数(*)

回答by Your Common Sense

As a matter of fact, neither PDO rowCount nor COUNT(*) is ever required here.

事实上，这里既不需要 PDO rowCount 也不需要 COUNT(*)。

if row >1 then fetch data

如果行> 1，则获取数据

is a faulty statement.
In a sanely designed web-application (I know it sounds like a joke for PHP) one don't have to make it this way.
Most sensible way would be

是错误的说法。
在一个设计合理的 Web 应用程序中（我知道这听起来像是 PHP 的一个笑话），不必这样做。
最明智的方法是

• to fetch first
• to use the fetched data

• if needed, we can use this very fetched data to see whether anything was returned:

  $data = $stmt->fetch();
  if($data){
      //use it whatever
  } else {
      echo "No record";
  }
  

• 先取
• 使用获取的数据

• 如果需要，我们可以使用这个非常获取的数据来查看是否返回了任何内容：

  $data = $stmt->fetch();
  if($data){
      //use it whatever
  } else {
      echo "No record";
  }
  

Easy, straightforward, and no questions like "which useless feature is better" at all.

简单、直接，完全没有诸如“哪个无用的功能更好”之类的问题。

In your case, assuming idis an unique index, only one row can be returned. Therefore, you don't need whilestatement at all. Just use the snippet above either to fetch and to tell whether enythin has been fetched.

在您的情况下，假设id是唯一索引，则只能返回一行。因此，您根本不需要while声明。只需使用上面的代码片段来获取并判断是否已获取 enythin。

In case many rows are expected, then just change fetch()to fetchAll()and then use foreach to iterate the returned array:

如果需要很多行，那么只需更改fetch()为fetchAll()然后使用 foreach 来迭代返回的数组：

$data = $stmt->fetchAll();
if($data){
    foreach ($data as $row) {
        //use it whatever
    }
} else {
    echo "No records";
}

Note that you should never select more rows than needed.Means your query on a regular web page should never return more rows than will be displayed.

请注意，您不应选择多于需要的行。意味着您在常规网页上的查询不应该返回比显示的更多的行。

Speaking of the question itself - it makes no sense. One cannot compare rowCountVS COUNT(*), because it's incomparable matters. These two functions has absolutely different purpose and cannot be interchanged:

说到问题本身——这毫无意义。无法比较 rowCountVS COUNT(*)，因为它是无与伦比的事情。这两个函数的用途完全不同，不能互换：

• COUNT(*)returns one single row with count, and have to be used ONLY if one need the count of records, but no records themselves.
  if you need the records, count(whatever)is not faster nor slower - it's pointless.
• rowCount()returns the number of already selected rowsand therefore you scarcely need it, as it was shown above.

• COUNT(*)返回带有计数的单行，并且只有在需要记录计数时才必须使用，但不需要记录本身。
  如果你需要记录，count(whatever)不是更快也不是更慢 - 这是毫无意义的。
• rowCount()返回已选择的行数，因此您几乎不需要它，如上所示。

Not to mention that the second example will fetch no rows at all.

更不用说第二个示例根本不会获取任何行。

回答by Nikhil

Performance difference should be negligible to null, since you are issuing only one query in both cases. The 2nd query has to fetch an extra column with the same value for every row matching id, hence it might have a large memory footprint. Even without the COUNT(*)the row count should be available, hence you should go with the 1st solution.

性能差异对于 null 应该可以忽略不计，因为在这两种情况下您都只发出一个查询。第二个查询必须为每行匹配获取一个具有相同值的额外列id，因此它可能占用大量内存。即使没有COUNT(*)行数也应该可用，因此您应该使用第一个解决方案。

About your 2nd question, AFAIK either COUNT(id)or COUNT(*)will be faster with the index on id, since the db engine will have to perform a range scan to retrieve the rows in question, and range scans are fasterwith indexes when filtering on the indexed column (in your case id = SOME_ID).

关于你的第二个问题，据我所知无论是COUNT(id)或COUNT(*)将是与指数更快id，因为数据库引擎必须执行一系列扫描，以获取有关行，范围扫描速度更快与索引列上的过滤索引时（在你的情况id = SOME_ID）。

回答by Kuzgun

Count(id) or count(*) will use index scan so it will be better for performance. Rowcount returns only affected rows and useful on insert/update/delete

Count(id) 或 count(*) 将使用索引扫描，因此性能会更好。Rowcount 只返回受影响的行，对插入/更新/删除很有用

EDIT:Since the question edited to compare Count(id) and count(), it makes a slight difference. Count() will return row count affected by select. Count(column) will return non null value count but since it is id, there wont be a null column. So it doesnt make difference for this case.

编辑：由于编辑了问题以比较 Count(id) 和 count( )，因此略有不同。Count() 将返回受选择影响的行数。Count(column) 将返回非空值计数，但由于它是 id，因此不会有空列。所以这对这种情况没有影响。

回答by Moeed Farooqui

Count(*)will be faster.

Count(*)会更快。

PDOStatement::rowCount()is not guaranteed to work according to the PDO documentation:

PDOStatement::rowCount()不能保证根据 PDO 文档工作：

"not guaranteed for all databases and should not be relied on for portable applications."

“不能保证适用于所有数据库，也不应依赖于便携式应用程序。”

So in your case I'd suggest using count(*).

所以在你的情况下，我建议使用count(*).

See reference: pdostatement.rowcount Manual

参见参考： pdostatement.rowcount 手册