Is there an effect on the speed of a query when using SQL_CALC_FOUND_ROWS in MySQL?

Question

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

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

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.

Answer 1

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.

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

---

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

Answer 3

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.

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.

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.

Answer 4

I would advise to use as less 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 it better at getting the actual information as re-querying. Btw. as the page mentions the command has some drawbacks too when used in replicated environments.

Answer 5

Similar to a previous question:

http://stackoverflow.com/questions/186588/which-is-fastest-select-sqlcalcfoundrows-from-table-or-select-count/