MySQL Forums
Forum List  »  Performance

Re: Oder by DESC on Indexed Column Extremely Slow
Posted by: Øystein Grøvlen
Date: June 13, 2017 05:20AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Oder by DESC on Indexed Column Extremely Slow
94
June 13, 2017 05:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.