在 MySQL 中使用 SQL_CALC_FOUND_ROWS 对查询速度有影响吗? [英] Is there an effect on the speed of a query when using SQL_CALC_FOUND_ROWS in MySQL?

问题描述

前几天我发现了 FOUND_ROWS() (here) 在 MySQL 中的函数及其对应的 SQL_CALC_FOUND_ROWS 选项.后者看起来特别有用(而不是运行第二个查询来获取行数).

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).

我想知道将 SQL_CALC_FOUND_ROWS 添加到查询会对速度产生什么影响?

I'm wondering what speed impact there is by adding SQL_CALC_FOUND_ROWS to a query?

我猜这比运行第二个查询来计算行数要快得多，但会不会有很大不同.此外，我发现限制查询以使其更快(例如，当您获得 1000 的前 10 行时).将 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?

我知道我可以测试这个，但我想知道这里的一般做法.

I know I can test this, but I'm wondering about general practices here.

推荐答案

为了计算 SQL_CALC_FOUND_ROWS 查询将被执行，就好像没有设置 LIMIT 一样，但是结果集发送到客户端将遵守LIMIT.

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.

更新:对于仅使用索引的 COUNT(*) 操作，SQL_CALC_FOUND_ROWS 速度较慢(参考).

Update: for COUNT(*) operations which would be using only the index, SQL_CALC_FOUND_ROWS is slower (reference).

这篇关于在 MySQL 中使用 SQL_CALC_FOUND_ROWS 对查询速度有影响吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！