1

I have a query which is running far slower than it should. I have distilled the problem down to a simple select statement (some fields have been renamed for privacy):

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS id, date_started, date_complete, status
FROM table_a
ORDER BY date DESC
LIMIT 0, 100

When SQL_CALC_FOUND_ROWS is used then query completes in about 0.70 seconds, however when SQL_CALC_FOUND_ROWS is removed then the query completes in about 0.0005 seconds (in both cases SQL_NO_CACHE is used in the query).

table_a has an index on the date field.

Apparently SQL_CALC_FOUND_ROWS can prevent an index from being used:

So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.

I have confirmed this. No index is used when SQL_CALC_FOUND_ROWS is included:

EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS id, date_started, date_complete, status FROM table_a ORDER BY date DESC limit 0, 100;
+----+-------------+-------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+-------------+------+---------------+------+---------+------+--------+----------------+
|  1 | SIMPLE      | table_a     | ALL  | NULL          | NULL | NULL    | NULL | 132208 | Using filesort | 
+----+-------------+-------------+------+---------------+------+---------+------+--------+----------------+

But when SQL_CALC_FOUND_ROWS is not used then the index on the date field is used:

EXPLAIN SELECT SQL_NO_CACHE id, date_started, date_complete, status FROM table_a ORDER BY date DESC limit 0, 100;
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-------+
| id | select_type | table       | type  | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | table_a     | index | NULL          | date | 13      | NULL | 132208 |       | 
+----+-------------+-------------+-------+---------------+------+---------+------+--------+-------+

Is there any way to speed the query up without removing SQL_CALC_FOUND_ROWS from the query?

I'm using MySQL version 5.0.51a-3ubuntu5.1-log.

mulllhausen
  • 4,225
  • 7
  • 49
  • 71
  • What exactly is your question? The percona answer you link to has an exhaustive answer. – pvg Jan 12 '16 at 01:28
  • Use separate `select count(*)` query. – sectus Jan 12 '16 at 01:38
  • the bold bit is the question. i'm basically asking if anybody knows of a workaround to alter mysql behaviour. i know i could do 2 queries, but this will be a lot of effort since a lot of queries in my code use sql_calc_found_rows, so i'm asking before i go and put a month's work in. – mulllhausen Jan 12 '16 at 01:38

0 Answers0