Re: Oder by DESC on Indexed Column Extremely Slow

[Øystein Grøvlen]

Hi,

According to EXPLAIN, the plan is to read 198 rows and sorting does not seem to be required. Hence, something is definitely wrong if it takes 30 minutes! Since multiple partitions are involved, some mechanism to merge the rows from the different partitions must be applied. Maybe there is some inefficiency wrt how this is done for descending order? If so, a work-around could be to do ascending sorting in a derived table, and then sort the result in the right order in the main query. In other words, something like:

SELECT * FROM
(SELECT * FROM transaction WHERE Account_ID='123' AND Generation_Timestamp
BETWEEN 1462041000 AND 1471285740 ORDER BY Generation_Timestamp ASC limit 200) dt
ORDER BY DESC LIMIT 200;

Also, I suggest to report a bug on this. Preferably with a repeatable test case.
It would also be interesting to know whether this problem only applies to partitioned tables, or whether it can be reproduced with non-partitioned tables, too.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway