When you limit the number of rows to be returned by a SQL query, usually used in paging, there are two methods to determine the total number of records:
当您限制SQL查询返回的行数(通常用于分页)时，有两种方法可以确定记录的总数:
Method 1
Include the SQL_CALC_FOUND_ROWS option in the original SELECT, and then get the total number of rows by running SELECT FOUND_ROWS():
在原来的SELECT中包含SQL_CALC_FOUND_ROWS选项，然后通过运行SELECT FOUND_ROWS()获取总行数:
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
Method 2
Run the query normally, and then get the total number of rows by running SELECT COUNT(*)
正常运行查询，然后运行SELECT COUNT(*)获取行总数
SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;
Which method is the best / fastest?
哪种方法最好/最快?
5 个解决方案
#1
104
It depends. See the MySQL Performance Blog post on this subject: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
视情况而定。请参阅本主题的MySQL Performance Blog文章:http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not- sql_calc_found_rows/
Just a quick summary: Peter says that it depends on your indexes and other factors. Many of the comments to the post seem to say that SQL_CALC_FOUND_ROWS is almost always slower - sometimes up to 10x slower - than running two queries.
简单总结一下:Peter说这取决于你的指标和其他因素。许多对这篇文章的评论似乎都说SQL_CALC_FOUND_ROWS几乎总是比运行两个查询慢，有时甚至慢10倍。
#2
13
When choosing the "best" approach, a more important consideration than speed might be the maintainability and correctness of your code. If so, SQL_CALC_FOUND_ROWS is preferable because you only need to maintain a single query. Using a single query completely precludes the possibility of a subtle difference between the main and count queries, which may lead to an inaccurate COUNT.
在选择“最佳”方法时，比速度更重要的考虑因素可能是代码的可维护性和正确性。如果是，SQL_CALC_FOUND_ROWS更可取，因为您只需要维护一个查询。使用单个查询完全排除了主查询和计数查询之间存在细微差异的可能性，这可能导致不准确的计数。
#3
10
According to the following article: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
根据以下文章:https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
If you have an INDEX on your where clause (if id is indexed in your case), then it is better not to use SQL_CALC_FOUND_ROWS and use 2 queries instead, but if you don't have an index on what you put in your where clause (id in your case) then using SQL_CALC_FOUND_ROWS is more efficient.
如果你有一个索引在where子句)(如果索引id,然后最好不要使用SQL_CALC_FOUND_ROWS使用2查询相反,但如果你没有一个索引你放在你的where子句(id在你的情况下),那么使用SQL_CALC_FOUND_ROWS更有效率。
#4
7
IMHO, the reason why 2 queries
IMHO, 2个查询的原因。
SELECT * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;
SELECT count(*) FROM count_test WHERE b = 666;
are faster than using SQL_CALC_FOUND_ROWS
比使用SQL_CALC_FOUND_ROWS更快吗
SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;
has to be seen as a particular case.
必须将其视为一个特例。
It in facts depends on the selectivity of the WHERE clause compared to the selectivity of the implicit one equivalent to the ORDER + LIMIT.
事实上，它取决于WHERE子句的选择性，而不是等同于ORDER + LIMIT的隐式子句的选择性。
As Arvids told in comment (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-1174394), the fact that the EXPLAIN use, or not, a temporay table, should be a good base for knowing if SCFR will be faster or not.
正如Arvids在评论中所说(http://www.mysqlperformanceblog.com/2007/08/28/to sql_calc_found_rows-or-not- sql_calc_found_row_rows/ #comment-1174394)， EXPLAIN use(或not)是一个临时表，应该是了解SCFR是否会更快的良好基础。
But, as I added (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-8166482), the result really, really depends on the case. For a particular paginator, you could get to the conclusion that “for the 3 first pages, use 2 queries; for the following pages, use a SCFR” !
但是，正如我所添加的(http://www.mysqlperformanceblog.com/2007/08/28/to sql_calc_found_rows-or-not- sql_calc_found_rows/#comment-8166482)，结果真的，真的，真的，取决于情况。对于一个特定的paginator，您可以得出这样的结论:“对于前三页，使用两个查询;对于以下页面，请使用SCFR !
#5
2
Removing some unnecessary SQL and then COUNT(*) will be faster than SQL_CALC_FOUND_ROWS. Example:
删除一些不必要的SQL然后计数(*)将比SQL_CALC_FOUND_ROWS更快。例子:
-
SELECT Person.Id, Person.Name, Job.Description, Card.Number - 选择的人。Id、人。姓名、工作。描述,Card.Number
-
FROM Person - 从一个人
-
JOIN Job ON Job.Id = Person.Job_Id - 加入工作工作。Id = Person.Job_Id
-
LEFT JOIN Card ON Card.Person_Id = Person.Id - 卡上的左连接卡。Person_Id = Person.Id
-
WHERE Job.Name = 'WEB Developer' - 在那里工作。Name = ' WEB开发人员
ORDER BY Person.Name- ORDER BY Person.Name
Then count without unnecessary part:
然后计算而不需要的部分:
SELECT COUNT(*)- SELECT COUNT(*)
FROM Person- 从一个人
JOIN Job ON Job.Id = Person.Job_Id- 加入工作工作。Id = Person.Job_Id
-
LEFT JOIN Card ON Card.Person_Id = Person.Id - 卡上的左连接卡。Person_Id = Person.Id
WHERE Job.Name = 'WEB Developer'- 在那里工作。Name = ' WEB开发人员
-
ORDER BY Person.Name - ORDER BY Person.Name
#1
104
It depends. See the MySQL Performance Blog post on this subject: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
视情况而定。请参阅本主题的MySQL Performance Blog文章:http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not- sql_calc_found_rows/
Just a quick summary: Peter says that it depends on your indexes and other factors. Many of the comments to the post seem to say that SQL_CALC_FOUND_ROWS is almost always slower - sometimes up to 10x slower - than running two queries.
简单总结一下:Peter说这取决于你的指标和其他因素。许多对这篇文章的评论似乎都说SQL_CALC_FOUND_ROWS几乎总是比运行两个查询慢，有时甚至慢10倍。
#2
13
When choosing the "best" approach, a more important consideration than speed might be the maintainability and correctness of your code. If so, SQL_CALC_FOUND_ROWS is preferable because you only need to maintain a single query. Using a single query completely precludes the possibility of a subtle difference between the main and count queries, which may lead to an inaccurate COUNT.
在选择“最佳”方法时，比速度更重要的考虑因素可能是代码的可维护性和正确性。如果是，SQL_CALC_FOUND_ROWS更可取，因为您只需要维护一个查询。使用单个查询完全排除了主查询和计数查询之间存在细微差异的可能性，这可能导致不准确的计数。
#3
10
According to the following article: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
根据以下文章:https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
If you have an INDEX on your where clause (if id is indexed in your case), then it is better not to use SQL_CALC_FOUND_ROWS and use 2 queries instead, but if you don't have an index on what you put in your where clause (id in your case) then using SQL_CALC_FOUND_ROWS is more efficient.
如果你有一个索引在where子句)(如果索引id,然后最好不要使用SQL_CALC_FOUND_ROWS使用2查询相反,但如果你没有一个索引你放在你的where子句(id在你的情况下),那么使用SQL_CALC_FOUND_ROWS更有效率。
#4
7
IMHO, the reason why 2 queries
IMHO, 2个查询的原因。
SELECT * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;
SELECT count(*) FROM count_test WHERE b = 666;
are faster than using SQL_CALC_FOUND_ROWS
比使用SQL_CALC_FOUND_ROWS更快吗
SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;
has to be seen as a particular case.
必须将其视为一个特例。
It in facts depends on the selectivity of the WHERE clause compared to the selectivity of the implicit one equivalent to the ORDER + LIMIT.
事实上，它取决于WHERE子句的选择性，而不是等同于ORDER + LIMIT的隐式子句的选择性。
As Arvids told in comment (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-1174394), the fact that the EXPLAIN use, or not, a temporay table, should be a good base for knowing if SCFR will be faster or not.
正如Arvids在评论中所说(http://www.mysqlperformanceblog.com/2007/08/28/to sql_calc_found_rows-or-not- sql_calc_found_row_rows/ #comment-1174394)， EXPLAIN use(或not)是一个临时表，应该是了解SCFR是否会更快的良好基础。
But, as I added (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-8166482), the result really, really depends on the case. For a particular paginator, you could get to the conclusion that “for the 3 first pages, use 2 queries; for the following pages, use a SCFR” !
但是，正如我所添加的(http://www.mysqlperformanceblog.com/2007/08/28/to sql_calc_found_rows-or-not- sql_calc_found_rows/#comment-8166482)，结果真的，真的，真的，取决于情况。对于一个特定的paginator，您可以得出这样的结论:“对于前三页，使用两个查询;对于以下页面，请使用SCFR !
#5
2
Removing some unnecessary SQL and then COUNT(*) will be faster than SQL_CALC_FOUND_ROWS. Example:
删除一些不必要的SQL然后计数(*)将比SQL_CALC_FOUND_ROWS更快。例子:
-
SELECT Person.Id, Person.Name, Job.Description, Card.Number - 选择的人。Id、人。姓名、工作。描述,Card.Number
-
FROM Person - 从一个人
-
JOIN Job ON Job.Id = Person.Job_Id - 加入工作工作。Id = Person.Job_Id
-
LEFT JOIN Card ON Card.Person_Id = Person.Id - 卡上的左连接卡。Person_Id = Person.Id
-
WHERE Job.Name = 'WEB Developer' - 在那里工作。Name = ' WEB开发人员
ORDER BY Person.Name- ORDER BY Person.Name
Then count without unnecessary part:
然后计算而不需要的部分:
SELECT COUNT(*)- SELECT COUNT(*)
FROM Person- 从一个人
JOIN Job ON Job.Id = Person.Job_Id- 加入工作工作。Id = Person.Job_Id
-
LEFT JOIN Card ON Card.Person_Id = Person.Id - 卡上的左连接卡。Person_Id = Person.Id
WHERE Job.Name = 'WEB Developer'- 在那里工作。Name = ' WEB开发人员
-
ORDER BY Person.Name - ORDER BY Person.Name