The other day I found the FOUND_ROWS() (here) function in MySQL and it's corresponding SQL_CALC_FOUND_ROWS option. The later looks especially useful (instead of running a second query to get the row count).
前几天我在MySQL中找到了FOUND_ROWS()(这里)函数，它是相应的SQL_CALC_FOUND_ROWS选项。后者看起来特别有用(而不是运行第二个查询来获取行数)。
I'm wondering what speed impact there is by adding SQL_CALC_FOUND_ROWS to a query?
我想知道通过向查询添加SQL_CALC_FOUND_ROWS会对速度产生什么影响?
I'm guessing it will be much faster than runnning a second query to count the rows, but will it be a lot different. Also, I have found limiting a query to make it much faster (for example when you get the first 10 rows of 1000). Will adding SQL_CALC_FOUND_ROWS to a query with a small limit cause the query to run much slower?
我猜这将比运行第二个查询来计数行要快得多，但会有很大的不同吗?此外，我还发现限制查询以使其速度更快(例如，当您获得前10行1000)。将SQL_CALC_FOUND_ROWS添加到一个有小限制的查询中，会导致查询运行得更慢吗?
I know I can test this, but I'm wondering about general practices here.
我知道我可以测试这个，但是我想知道这里的通用实践。
4 个解决方案
#1
2
To calculate SQL_CALC_FOUND_ROWS the query will be execute as if no LIMIT was set, but the result set sent to the client will obey the LIMIT.
要计算SQL_CALC_FOUND_ROWS，查询将像没有设置限制一样执行，但发送给客户端的结果集将遵守该限制。
Update: for COUNT(*) operations which would be using only the index, SQL_CALC_FOUND_ROWS is slower (reference).
更新:对于只使用索引的COUNT(*)操作，SQL_CALC_FOUND_ROWS要慢一些(引用)。
#2
2
When I was at the MySQL Conference in 2008, part of one session was dedicated to exactly this - benchmarks between SQL_CALC_FOUND_ROWS and doing a separate SELECT.
当我在2008年的MySQL会议上时，有一个会话专门讨论这个问题——SQL_CALC_FOUND_ROWS之间的基准测试和单独的选择。
I believe the result was that there was no benefit to SQL_CALC_FOUND_ROWS - it wasn't faster, in fact it may have been slower. There was also a 3rd way.
我认为结果是，SQL_CALC_FOUND_ROWS没有任何好处——它不是更快，实际上可能更慢。还有第三条路。
Additionally, you don't always need this information, so I would go the extra query route.
此外，您并不总是需要这些信息，所以我将使用额外的查询路径。
I'll try to find the slides...
我会尽量找幻灯片…
Edit: Hrm, google tells me that I actually liveblogged from that session: http://beerpla.net/2008/04/16/mysql-conference-liveblogging-mysql-performance-under-a-microscope-the-tobias-and-jay-show-wednesday-200pm/. Google wins when memory fails.
编辑:Hrm，谷歌告诉我，我实际上是在博客中度过的:http://beerpla.net/2008/04/16/mysql- liveblogging-mysql-performance-under- microscope tobiasand jae -show-周三-200pm/。当内存失败时谷歌获胜。
#3
0
I assume it would be slightly faster for queries that you need the number of rows know, but would incur and overhead for queries that you don't need to know.
我认为，对于需要知道行数的查询，它会稍微快一些，但是对于不需要知道的查询，它会导致开销。
The best advice I could give is to try it out on your development server and benchmark the difference. Every setup is different.
我能给出的最佳建议是在您的开发服务器上尝试它，并对差异进行基准测试。每一个设置是不同的。
#4
0
I would advise to use as few proprietary SQL extensions as possible when developing an application (or actually not using SQL queries at all). Doing a separate query is portable, and actually I don't think MySql could do better at getting the actual information than re-querying. Btw. as the page mentions the command has some drawbacks too when used in replicated environments.
在开发应用程序时，我建议尽可能少使用私有SQL扩展(或者根本不使用SQL查询)。单独执行查询是可移植的，实际上我认为MySql在获取实际信息方面没有比重新查询更好的。顺便说一句。正如页面提到的，在复制环境中使用该命令也有一些缺点。
#1
2
To calculate SQL_CALC_FOUND_ROWS the query will be execute as if no LIMIT was set, but the result set sent to the client will obey the LIMIT.
要计算SQL_CALC_FOUND_ROWS，查询将像没有设置限制一样执行，但发送给客户端的结果集将遵守该限制。
Update: for COUNT(*) operations which would be using only the index, SQL_CALC_FOUND_ROWS is slower (reference).
更新:对于只使用索引的COUNT(*)操作，SQL_CALC_FOUND_ROWS要慢一些(引用)。
#2
2
When I was at the MySQL Conference in 2008, part of one session was dedicated to exactly this - benchmarks between SQL_CALC_FOUND_ROWS and doing a separate SELECT.
当我在2008年的MySQL会议上时，有一个会话专门讨论这个问题——SQL_CALC_FOUND_ROWS之间的基准测试和单独的选择。
I believe the result was that there was no benefit to SQL_CALC_FOUND_ROWS - it wasn't faster, in fact it may have been slower. There was also a 3rd way.
我认为结果是，SQL_CALC_FOUND_ROWS没有任何好处——它不是更快，实际上可能更慢。还有第三条路。
Additionally, you don't always need this information, so I would go the extra query route.
此外，您并不总是需要这些信息，所以我将使用额外的查询路径。
I'll try to find the slides...
我会尽量找幻灯片…
Edit: Hrm, google tells me that I actually liveblogged from that session: http://beerpla.net/2008/04/16/mysql-conference-liveblogging-mysql-performance-under-a-microscope-the-tobias-and-jay-show-wednesday-200pm/. Google wins when memory fails.
编辑:Hrm，谷歌告诉我，我实际上是在博客中度过的:http://beerpla.net/2008/04/16/mysql- liveblogging-mysql-performance-under- microscope tobiasand jae -show-周三-200pm/。当内存失败时谷歌获胜。
#3
0
I assume it would be slightly faster for queries that you need the number of rows know, but would incur and overhead for queries that you don't need to know.
我认为，对于需要知道行数的查询，它会稍微快一些，但是对于不需要知道的查询，它会导致开销。
The best advice I could give is to try it out on your development server and benchmark the difference. Every setup is different.
我能给出的最佳建议是在您的开发服务器上尝试它，并对差异进行基准测试。每一个设置是不同的。
#4
0
I would advise to use as few proprietary SQL extensions as possible when developing an application (or actually not using SQL queries at all). Doing a separate query is portable, and actually I don't think MySql could do better at getting the actual information than re-querying. Btw. as the page mentions the command has some drawbacks too when used in replicated environments.
在开发应用程序时，我建议尽可能少使用私有SQL扩展(或者根本不使用SQL查询)。单独执行查询是可移植的，实际上我认为MySql在获取实际信息方面没有比重新查询更好的。顺便说一句。正如页面提到的，在复制环境中使用该命令也有一些缺点。