MySQL Forums
Forum List  »  Performance

Oder by DESC on Indexed Column Extremely Slow
Posted by: Nithya Kodithuwakku Ranadeerage
Date: June 08, 2017 11:34PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Oder by DESC on Indexed Column Extremely Slow
49
June 08, 2017 11:34PM


Sorry, only registered users may post in this forum.

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.