0

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.

Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047

1 Answers1

1

SQL_CALC_FOUND_ROWS should go before distinct:

SELECT SQL_CALC_FOUND_ROWS DISTINCT res.*,...GROUP BY...HAVING...ORDER BY fqa_value LIMIT 0,10;
SELECT FOUND_ROWS() as count
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • https://dev.mysql.com/doc/refman/5.7/en/select.html says the opposite, DISTINCT before SQL_CALC_FOUND_ROWS – konyak Feb 28 '19 at 20:19