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.