哪个最快?从“表"中选择SELECT SQL_CALC_FOUND_ROWS,或选择COUNT(*) [英] Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
问题描述
当限制通常用于分页的SQL查询返回的行数时,有两种方法可确定记录总数:
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:
在原始的SELECT
中包含SQL_CALC_FOUND_ROWS
选项,然后通过运行SELECT FOUND_ROWS()
获得行的总数:
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 * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
方法2
正常运行查询,然后通过运行SELECT COUNT(*)
SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;
哪种方法最好/最快?
推荐答案
这要视情况而定.请参阅有关此主题的MySQL Performance Blog帖子: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
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/
快速摘要:彼得说,这取决于您的索引和其他因素.帖子中的许多评论似乎都说SQL_CALC_FOUND_ROWS几乎总是比运行两个查询慢-有时甚至慢10倍.
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.
这篇关于哪个最快?从“表"中选择SELECT SQL_CALC_FOUND_ROWS,或选择COUNT(*)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!