Oder by DESC on Indexed Column Extremely Slow
Hi,
I have a InnoDB table with about 1 Billion Records on MySQL server v5.7 .
And I have indexed some columns as well. The following is a part of the create table command.
CREATE TABLE `transaction` (
`id` bigint(12) NOT NULL AUTO_INCREMENT,
`Access_Point` varchar(130) DEFAULT NULL,
`Account_ID` bigint(16) NOT NULL,
...
`Generation_Timestamp` int(11) NOT NULL,
...
PRIMARY KEY (`id`,`Account_ID`,`Generation_Timestamp`),
UNIQUE KEY `Account_ID` (`Account_ID`,`Generation_Timestamp`,`Event_Label`,`Event_Result`),
KEY `idx_transaction_msisdn_timestamp` (`Account_ID`,`Generation_Timestamp`),
KEY `idx_transaction_timestamp` (`Generation_Timestamp`);
And I have created daily partitions using Generation_Timstamp as following.
PARTITION BY RANGE (Generation_Timestamp)
PARTITION `2016-07-01` VALUES LESS THAN (1467397800),
PARTITION `2016-07-02` VALUES LESS THAN (1467484200)
...
The Issue is when I run a query with `ORDER BY Generation_Timestamp ASC limit 200` the Results are retrievied within 100 milliseconds.
But If I change the query to `ORDER BY Generation_Timestamp DESC limit 200` the Query takes about 30 minutes to return the results.
The Complete query is as following.
SELECT * FROM transaction WHERE Account_ID='123' AND Generation_Timestamp
BETWEEN 1462041000 AND 1471285740 ORDER BY Generation_Timestamp DESC limit 200.
Any Idea why this issue is present. I know that the indexed are sorted in ASC order. But is there anyway That I can improve the performance?
Following is the Explain output.
explain SELECT * FROM transaction WHERE Account_ID='123' AND Generation_Timestamp BETWEEN 1462041000 AND 1471285740 ORDER BY Generation_Timestamp DESC limit 200 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: transaction
partitions: 2016-07-01,2016-07-02,2016-07-03,2016-07-04,2016-07-05,2016-07-06,2016-07-07,2016-07-08,2016-07-09,2016-07-10,2016-07-11,2016-07-12,2016-07-13,2016-07-14,2016-07-15,2016-07-16,2016-07-17,2016-07-18,2016-07-19,2016-07-20,2016-07-21,2016-07-22,2016-07-23,2016-07-24,2016-07-25,2016-07-26,2016-07-27,2016-07-28,2016-07-29,2016-07-30,2016-07-31,2016-08-01,2016-08-02,2016-08-03,2016-08-04,2016-08-05,2016-08-06,2016-08-07,2016-08-08,2016-08-09,2016-08-10,2016-08-11,2016-08-12,2016-08-13,2016-08-14,2016-08-15,2016-08-16
type: range
possible_keys: Account_ID,idx_transaction_msisdn_timestamp,idx_transaction_timestamp
key: Account_ID
key_len: 12
ref: NULL
rows: 198
filtered: 100.00
Extra: Using index condition
Any help is Greatly appreciated.