We have in our application two queries, the first very long which takes 30 seconds, and then another to get the number of rows before the LIMIT:
SELECT DISTINCT SQL_CALC_FOUND_ROWS res.*,...GROUP BY...HAVING...ORDER BY fqa_value LIMIT 0,10;
SELECT FOUND_ROWS() as count
We can optimize the speed from 30 seconds down to 1 second if we take out the "ORDER BY fqa_value".
So I put everything in a subselect and then sort it:
select * from (
SELECT DISTINCT SQL_CALC_FOUND_ROWS res.*,...GROUP BY...HAVING...LIMIT 0,10;
) as temptable order by fqa_value;
However this gives me the error: "Incorrect usage/placement of SQL_CALC_FOUND_ROWS".
If I take the SQL_CALC_FOUND_ROWS out, it works:
select * from (
SELECT DISTINCT res.*,...GROUP BY...HAVING...ORDER BY fqa_value LIMIT 0,10
) as temptable order by fqa_value;
But then I don't have the original number of rows that was selected before GROUP BY and HAVING.
How can I both (a) count the original rows, and (b) have a fast query? I'm looking preferably for a pure MySQL solution so we don't have to change the code if necessary.