WL#12615: Deprecate SQL_CALC_FOUND_ROWS and FOUND_ROWS

MySQL has a nonstandard query modifier called SQL_CALC_FOUND_ROWS. When in use on a SELECT with LIMIT, it attempts to calculate how many rows would have been returned if the limit were not there, and then store that for later retrieval in FOUND_ROWS().

SQL_CALC_FOUND_ROWS has a number of problems. First of all, it's slow. Frequently, it would be cheaper to run the query with LIMIT and then a separate SELECT COUNT(*) for the same query, since COUNT(*) can make use of optimizations that can't be done when searching for the entire result set (e.g. filesort can be skipped for COUNT(*), whereas with CALC_FOUND_ROWS, we must disable some filesort optimizations to guarantee the right result). If you search online, you'll frequently find the advice to run two separate queries instead of using SQL_CALC_FOUND_ROWS, precisely for performance reasons.

More importantly, it has very unclear semantics in a number of situations. In particular, when a query has multiple query blocks (e.g. with UNION), there's simply no way to calculate the number of “would-have-been” rows at the same time as producing a valid query. As the iterator executor is progressing towards these kinds of queries, it is genuinely difficult to try to retain the same semantics. Furthermore, if there are multiple LIMITs in the query (e.g. for derived tables), it's not necessarily clear to which of them SQL_CALC_FOUND_ROWS should refer to. Thus, such nontrivial queries will necessarily get different semantics in the iterator executor compared to what they had before.

Finally, most of the use cases where SQL_CALC_FOUND_ROWS would seem useful should simply be solved by other mechanisms than LIMIT/OFFSET. E.g., a phone book should be paginated by letter (both in terms of UX and in terms of index use), not by record number. Discussions are increasingly infinite-scroll ordered by date (again allowing index use), not by paginated by post number. And so on.

The request is to output a deprecating warning whenever a query is run with SQL_CALC_FOUND_ROWS, and then have a separate later worklog for removal. FOUND_ROWS(), which is only used for getting the result of SQL_CALC_FOUND_ROWS, can be deprecated along with it.

F1. When writing a query with SQL_CALC_FOUND_ROWS or FOUND_ROWS(), there should be a warning (both with the standard deprecation warning code 1287):

Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.

Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.