php SELECT COUNT() vs mysql_num_rows();
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12864557/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT COUNT() vs mysql_num_rows();
提问by rinchik
I have a large table (60+) millions of records.
我有一个大表（60+）数百万条记录。
I'm using PHP script to navigate through this table.
我正在使用 PHP 脚本来浏览此表。
PHP script (with pagination) loads very fast because:
PHP 脚本（带分页）加载速度非常快，因为：
The table engine is InnoDB thus SELECT COUNT()is very slow and mysql_num_rows()is not an option, so i keep the total row count (the number that i use to generate pagination) in a separate table (i update this record total_rows=total_rows-1and total_rows=total_rows1+1during DELETEand INSERT).
表引擎是 InnoDB，因此SELECT COUNT()非常慢并且mysql_num_rows()不是一个选项，所以我将总行数（我用来生成分页的数字）保存在一个单独的表中（我更新此记录total_rows=total_rows-1并total_rows=total_rows1+1在DELETE和期间INSERT）。
But the question is what to do with the pagination for search results?
但问题是如何处理搜索结果的分页？
Right now I'm doing this with 2 steps:
现在我通过两个步骤来做到这一点：
1.
1.
$condition = " fname='rinchik' ";
$result = "SELECT * FROM my_large_table WHERE" . $condition;
Here i got all search results from DataBase.
在这里，我从数据库中获得了所有搜索结果。
2. Now i need to count these results to create pagination. I'm doing this:
2. 现在我需要计算这些结果来创建分页。我这样做：
$condition; <- we already have this from the step 1
$result_count = "SELECT COUNT(id) FROM my_large_table WHERE" . $condition;
And it's kinda slow.
而且速度有点慢。
Would it be better if i will do it this way (with just one step)?:
如果我这样做（只需一步）会更好吗？：
$condition = " fname='rinchik' ";
$result = "SELECT * FROM my_large_table WHERE" . $condition;
$result_count = mysql_num_rows($result);
回答by Matthew
Use COUNT, internally the server will process the request differently.
使用COUNT，服务器内部会以不同的方式处理请求。
When doing COUNT, the server will only allocate memory to store the result of the count.
这样做时COUNT，服务器只会分配内存来存储计数的结果。
When using mysql_num_rows, the server 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.
使用时mysql_num_rows，服务器将处理整个结果集，为所有这些结果分配内存，并将服务器置于获取模式，这涉及许多不同的细节，例如锁定。
Think of it like the following pseudo scenarios:
把它想象成以下伪场景：
SELECT COUNT(*)
SELECT COUNT(*)
Hey Bob, how many people are in the class room?
嘿鲍勃，教室里有多少人？
mysql_num_rows
mysql_num_rows
Hey Bob, send all the people from the classroom over to me, ... I'll count them to get the number of people myself
嘿鲍勃，把教室里的所有人都派给我，......我会自己计算他们的人数
In summary, when using mysql_num_rowsyou are transferring all records to the client, and the client will have to calculate the count itself.
总之，使用时mysql_num_rows您将所有记录传输到客户端，并且客户端必须自己计算计数。
回答by CappY
Use COUNT(id). It only returns the count, With mysql_num_rows($result);php fetch ALL the data from the mysql and count the number of found results.
使用 COUNT(id)。它只返回计数，使用mysql_num_rows($result);php 从 mysql 中获取所有数据并计算找到的结果数。
And finally, don't use mysql_* functions.
最后，不要使用 mysql_* 函数。
Suggested alternatives
Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:
mysqli_stmt_num_rows() PDOStatement::rowCount()
建议的替代品
不鼓励使用此扩展。相反，应使用 MySQLi 或 PDO_MySQL 扩展。另请参阅 MySQL：选择 API 指南和相关常见问题以了解更多信息。此功能的替代方案包括：
mysqli_stmt_num_rows() PDOStatement::rowCount()
回答by Alireza.A
Tested in inoDBengine and mysql 5.5.
在inoDB引擎和 mysql 5.5 中测试。
The idhas index and I think this is very fast
在id具有指数，我认为这是非常快
$q = "SELECT count(`id`) FROM table where 1";
$rows = mysql_query($q);
$count = mysql_fetch_array($rows);
echo $count[0];
if you want more, you have to use one index just on idor what ever you want to select.
如果您想要更多，您必须仅使用一个索引id或您想要选择的任何索引。
Caching is another solution and you can select from 1 set of records in few milliseconds!
缓存是另一种解决方案，您可以在几毫秒内从一组记录中进行选择！
