Note the asc/desc of each field using the BTREE composite Index to optimize the efficiency of the ORDER by query

tbl_direct_pos_201506 table has 1.9 million data, DDL:

CREATE TABLE ' tbl_direct_pos_201506 ' (' Acq_ins_code ' char () not NULL DEFAULT ' COMMENT ' agency code ', ' Trace_num ' char (6) N OT null default ' COMMENT ' tracking number ', ' trans_datetime ' char (TEN) not NULL default ' COMMENT ' Trading time ', ' Process_flag ' char (1) D Efault NULL COMMENT ' process identity ', ' Rev_flag ' char (1) default NULL COMMENT ' Receive Identity ', ' Before_trans_code ' char (3) default NULL COM ment ' transaction type ', ' Trans_amt ' decimal (15,3) default null COMMENT ' Transaction amount ', ' acct_num ' char (+) default NULL COMMENT ' card number ', ' Mer _type ' char (4) default null COMMENT ' merchant type ', ' Recv_ins_code ' char (all) default null COMMENT ' issuing line code ', ' Retrivl_ref_num ' Cha R () default NULL COMMENT ' Retrieve reference number ', ' Resp_auth_code ' char (6) default NULL COMMENT ' Authorization code ', ' Resp_code ' char (2) Default NUL L COMMENT ' ack code ', ' term_id ' char (8) default NULL COMMENT ' Terminal code ', ' Mer_code ' char (all) default null COMMENT ' Merchant code ', ' Mer_a Ddr_name ' char (max) default null COMMENT ' Merchant name and address, first 25 bytes is name, followed by address ', ' self_define ' varchar ' default null COMMENT ' 25th ' 9 Bytes is the card type ', ' sys_date 'CHAR (8) Not NULL default ' COMMENT ' trading date ', ' sa_sav2 ' varchar ' default NULL COMMENT ' No. 243 byte is DCC id ', ' rec_create_ Time ' datetime default null COMMENT ' online TimeSpan ', ' rec_update_time ' datetime default NULL COMMENT ' Last modified ', PRIMARY KEY (' sy S_date ', ' trans_datetime ', ' acq_ins_code ', ' trace_num '), KEY ' Idx_direct_pos_create_time ' (' Rec_create_time ')) ENGINE =myisam DEFAULT Charset=utf8 comment= ' Trading month table template ';

A slow query log for this table is as follows:
# time:150701 15:45:28
# [email protected]: test[test] @ localhost [127.0.0.1] id:1
# query_time:2.478195 lock_time:0.010007 rows_sent:20 rows_examined:450612
SET timestamp=1435736728;
Select Substr (T.acq_ins_code, 3) Meracqinscode, T.mer_code, t.term_id, substr (T.mer_addr_name, 1,) Mername,
Tt.trans_desc, T.rev_flag, T.trans_amt, concat (substr (t.sys_date, 1, 4), t.trans_datetime) transtime,t.before_trans_ Code
T.acct_num, T.retrivl_ref_num, T.resp_code, T.resp_auth_code, R.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_ Datetime
Case substr (t.sa_sav2,259,1) While 1 then ' debit card ' when 2 Then ' credit card '
When 3 Then ' quasi credit card ' when 4 Then ' private prepaid card ' Else ' end cardtype,
Case
When locate (' VIS ', t.sa_sav2) > 0 Then ' VISA '
When locate (' JCB ', t.sa_sav2) > 0 Then ' JCB '
When locate (' DNC ', t.sa_sav2) > 0 Then ' Diners '
When locate (' cups ', t.sa_sav2) > 0 Then ' UnionPay Territory card '
When locate (' UPI ', t.sa_sav2) > 0 Then ' UnionPay overseas card '
Else ' End Cardbrand
From tbl_direct_pos_201506 t
Left join Trans_recv_ins r on R.recv_ins_code = T.recv_ins_code
Left JOIN Tbl_trans_type tt on tt.trans_code = T.before_trans_code
where t.sys_date between ' 20150622 ' and ' 20150628 '
ORDER BY
T.sys_date desc, t.trans_datetime desc, T.acq_ins_code, T.trace_num
Limit 0, 20;
The log shows that the execution time of the SQL is 2.478 s.
Let's take a look at the SQL execution plan:

ID	Select_type	Table	type	Possible_keys	Key	Key_len	ref	rows	Extra
1	Simple	T	Range	PRIMARY	PRIMARY	24		299392	Using index condition; Using Filesort
1	Simple	R	Eq_ref	PRIMARY	PRIMARY	41	Msp.t.recv_ins_code	1	Using where
1	Simple	Tt	Eq_ref	PRIMARY	PRIMARY	14	Msp.t.before_trans_code	1	Using where

Execution Plan Analysis:
Using Filesort. Yes, seeing it shows that our query needs to be optimized: The file sort is sorted by the corresponding sorting algorithm, and the obtained data is ordered in memory.
The primary key and non-primary key indexes of the MyISAM storage engine differ very little, except that the index key of the primary key index is a unique and non-null key. The MyISAM index defaults to B-tree. In other words, the primary key here is equivalent to a normal b-tree.
The SQL a where field, four order by fields, are in the primary key, and order by is exactly the same as the leftmost prefix principle, why Filesort?
The MYSQL index creation manual says:
the definition of an indexed column can follow ASC or DESC. These keywords allow for future extensions to be used to specify ascending or descending index value storage. This syntax is parsed but ignored. Indexed columns are sorted by default in ascending order. --that is, you write no error, but write a white write.
In this way, our primary key is not sorted because our primary key is the ASC store for each primary key field, and the order by DESC and ASC (the default is ASC) are mixed. To verify this, we change the order by to the ASC with the primary key:

Select Substr (T.acq_ins_code, 3) Meracqinscode, T.mer_code, t.term_id, substr (T.mer_addr_name, 1,) mername,tt.trans_ Desc, T.rev_flag, T.trans_amt, concat (substr (t.sys_date, 1, 4), t.trans_datetime) Transtime,t.before_trans_code, T.acct_num, T.retrivl_ref_num, T.resp_code, T.resp_auth_code, R.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_ Datetime,case substr (t.sa_sav2,259,1) when the 1 then ' debit card ' when 2 Then ' credit card ' when 3 then ' quasi credit card ' when 4 Then ' private prepaid card ' else ' E nd cardtype,case when locate (' VIS ', t.sa_sav2) > 0 Then ' VISA ' to locate (' JCB ', t.sa_sav2) > 0 Then ' JCB ' when Loca  Te (' DNC ', t.sa_sav2) > 0 Then ' Diners ' when locate (' cups ', t.sa_sav2) > 0 Then ' UnionPay Territory card ' when locate (' UPI ', t.sa_sav2) > 0 Then ' UnionPay overseas card ' else ' end Cardbrand from tbl_direct_pos_201506 Tleft join Trans_recv_ins r on R.recv_ins_code = T.recv_ins_ Codeleft join Tbl_trans_type tt on tt.trans_code = T.before_trans_codewhere t.sys_date between ' 20150622 ' and ' 20150628 ' O Rder by T.sys_date, T.trans_datetime, t.acq_Ins_code, T.trace_numlimit 0, 20; 

Execution time: 0.023 S.
The results were not satisfactory. To view its execution plan:

ID	Select_type	Table	type	Possible_keys	Key	Key_len	ref	rows	Extra
1	Simple	T	Range	PRIMARY	PRIMARY	24		299392	Using Index Condition
1	Simple	R	Eq_ref	PRIMARY	PRIMARY	41	Msp.t.recv_ins_code	1	Using where
1	Simple	Tt	Eq_ref	PRIMARY	PRIMARY	14	Msp.t.before_trans_code	1	Using where

Sure enough, we used the primary key index and the using Filesort was gone.
Now that the problem has been found, the next thing seems to be just the process.
Asked the next business, paging results in Sys_date and trans_datetime two fields must be sorted in descending order, the remaining two fields are not very concerned.
Since we cannot change the descending and ascending order of each column of the index (ascending by default), we can make the index fields descending/ascending in the same order by the time we write the order by. The final SQL rewrite is:

Select Substr (T.acq_ins_code, 3) Meracqinscode, T.mer_code, t.term_id, substr (T.mer_addr_name, 1,) mername,tt.trans_ Desc, T.rev_flag, T.trans_amt, concat (substr (t.sys_date, 1, 4), t.trans_datetime) Transtime,t.before_trans_code, T.acct_num, T.retrivl_ref_num, T.resp_code, T.resp_auth_code, R.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_ Datetime,case substr (t.sa_sav2,259,1) when the 1 then ' debit card ' when 2 Then ' credit card ' when 3 then ' quasi credit card ' when 4 Then ' private prepaid card ' else ' E nd cardtype,case when locate (' VIS ', t.sa_sav2) > 0 Then ' VISA ' to locate (' JCB ', t.sa_sav2) > 0 Then ' JCB ' when Loca  Te (' DNC ', t.sa_sav2) > 0 Then ' Diners ' when locate (' cups ', t.sa_sav2) > 0 Then ' UnionPay Territory card ' when locate (' UPI ', t.sa_sav2) > 0 Then ' UnionPay overseas card ' else ' end Cardbrand from tbl_direct_pos_201506 Tleft join Trans_recv_ins r on R.recv_ins_code = T.recv_ins_ Codeleft join Tbl_trans_type tt on tt.trans_code = T.before_trans_codewhere t.sys_date between ' 20150622 ' and ' 20150628 ' O Rder by t.sys_date Desc, T.trans_datetime deSC, T.acq_ins_code desc, t.trace_num desc limit 0, 20; 

Execute it, 0.029 s, fix it.

Resources

• Http://dev.mysql.com/doc/refman/5.5/en/create-index.html
• Http://stackoverflow.com/questions/10109108/how-do-i-create-a-desc-index-in-mysql
• Http://stackoverflow.com/questions/2341576/updating-mysql-primary-key

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Note the asc/desc of each field using the BTREE composite Index to optimize the efficiency of the ORDER by query