Allow SQL_CALC_FOUND_ROWS with FOUND_ROWS() instead of SELECT COUNT(*)

[eelco2k]

I've noticed that when having complex DataTables with a lot of joins and filtering, the Count(*) for counting the result rows is slower than the complex query itself. So it would be nice to have an alternative method to use when counting the resulting rows by using the SQL_CACL_FOUND_ROWS in the select. and then afterwards use FOUND_ROWS() query.

i've optimized my tables with correct indexes. And when running the complex query it's only +-20ms. but when the select count(*) from (the_fast_but_complex_ query); runs it's more like 800ms.

But when i do the SELECT SQL_CALC_FOUND_ROWS, * FROM fast_but_complex_query JOIN some_difficult joins etc. It's +- 20 ms, and the FOUND_ROWS(); query afterwards is only +-5ms.

Which is a major performance hit.

[eelco2k]

furthermore i did disabled the SQL caching for both count(*) and SQL_CALC_FOUND_ROWS methods for performance measurements:

select 
 SQL_NO_CACHE  
  count(*) as aggregate
from
  ( ) count_row_table;

AND

SELECT  SQL_NO_CACHE SQL_CALC_FOUND_ROWS from ...... ; 
SELECT FOUND_ROWS();

i'm using 20 joins tables, 2 where clauses and a single order_by columnA desc;

i'm having combined indexes on the where and order_by column(s)

[yajra]

Thank you for the suggestions. If you can, please do not hesitate to submit a PR. I may not be able to dig this further atm.

[miken32]

This feature is non-standard and has been deprecated by MySQL as of 8.0.17. https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows