哪个最快?SELECT SQL_CALC_FOUND_ROWS FROM `table`，或 SELECT 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();  

方法二

正常运行查询，然后通过运行SELECT COUNT(*)

SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;  

哪种方法最好/最快?

推荐答案

视情况而定.请参阅有关此主题的 MySQL 性能博客文章:要SQL_CALC_FOUND_ROWS 还是不SQL_CALC_FOUND_ROWS?

It depends. See the MySQL Performance Blog post on this subject: 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 FROM `table`，或 SELECT COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！