問題描述

我有一個查詢使用 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(*)

這篇關于PDO::rowCount VS COUNT(*)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！