How to get total rows in mysql if there was no LIMIT clause?
How to get total rows in mysql if there was no LIMIT clause?
I was using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS();
But this has been deprecated https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows
The recommended way now is first to query with limit and then again without it selecting count(*).
My query is a bit complex and joins a couple of tables with a large number of records, which makes each select take up to 4 seconds, so my process now takes double the time compared to as I just keep using found rows.
How can I go back to just running the select a single time and still getting the total number of rows found without the limit?
Select count(*) from (select ...) ? See if the query optimizer pipelines that.
Maybe I misunderstand what you are trying to do.
The OP wants the paginated table data and the count. With a single query execution.
In normal SQL you'd use a window function to do this but I don't know if mysql supports that.
Would it work to write the query as a common table expression, then select your columns from that table and join it with a
count(*)aggregation of the table?As per mysql documentation:
The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated; expect them to be removed in a future version of MySQL. Execute the query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.
https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_found-rows