SQL_FOUND_ROWS() Makes query slow

[kartisathis]

Hi everyone,

select sql_calc_found_rows DIST_NO from acquired  WHERE FOLIO_NO='l01917' And STAT_FLAG <> 'Matched' union select  DIST_NO from transfrd   WHERE FOLIO_NO='l01917' And STAT_FLAG <> 'Matched'  LIMIT 0, 5;

The above query takes more than 4.5secs

if i run the query without sql_calc_found_rows the execution time is 0.7 secs

select DIST_NO from acquired   WHERE FOLIO_NO='l01917' And STAT_FLAG <> 'Matched' union select  DIST_NO from transfrd   WHERE FOLIO_NO='l01917' And STAT_FLAG <> 'Matched'  LIMIT 0, 5;

the total records found in both acquired and transfrd table is more than 25 lakhs how to overcome this issue and one more thing is here i indexed both FOLIO_NO and STAT_FLAG in both the tables

[smantscheff]

Show us the result of
EXPLAIN
for both cases.
Make sure that you have a query cache set up. If that does not help, you cannot speed up the query, but you can cache the results of SQL_CALC_FOUND_ROWS in a variable and display it until major changes happen to your data.

[kartisathis]

this is the result while am explain the query

+----+--------------+--------------+------+--------------------+----------+---------+-------+--------+-------------+
| id | select_type  | table        | type | possible_keys      | key      | key_len | ref   | rows   | Extra       |
+----+--------------+--------------+------+--------------------+----------+---------+-------+--------+-------------+
|  1 | PRIMARY      | acquired_tbl | ref  | FOLIO_NO,STAT_FLAG | FOLIO_NO | 103     | const | 136813 | Using where |
|  2 | UNION        | transfrd_tbl | ref  | FOLIO_NO,STAT_FLAG | FOLIO_NO | 103     | const | 880229 | Using where |
| NULL | UNION RESULT | <union1,2>   | ALL  | NULL               | NULL     | NULL    | NULL  |   NULL |             |
+----+--------------+--------------+------+--------------------+----------+---------+-------+--------+-------------+