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

The 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 ' Retrieval reference ', ' 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 for example the following:
# 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 can see that the SQL run time is 2.478 S.

Let's take a look at the SQL Run schedule:

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

Run Plan Analysis:
Using Filesort. Yes, see it. This indicates that our query needs to be optimized: The sorting of files is done by the corresponding sorting algorithm, and the obtained data is sorted in memory.

The primary key and non-primary key indexes of the MyISAM storage engine differ very little, just that the index key of the primary key index is a unique and non-null key.

MyISAM's index is b-tree. In other words, the primary key here is equivalent to a normal b-tree.
The SQL one where field, four order by fields. It's all in the primary key, and the order by is completely in line with the leftmost prefix principle, why Filesort?
MYSQL index creation manual says:
The definition of an indexed column can be followed by ASC or DESC. These keyword agree for future extensions to be used to specify ascending or descending index value storage. This syntax is parsed but ignored. Indexed columns are always sorted in ascending order.

--that is, you write no error, but write a white write.

In this way, our primary key does not have a sort effect. The reason is that our primary key is the ASC store for each primary key field. The ORDER by DESC and ASC (the default is ASC) are mixed. In order to verify this statement. 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; 

Run time: 0.023 S.

The results were not satisfactory. To view its run schedule:

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, pagination results in sys_date and trans_datetime two fields must be sorted in descending order. The rest of the two fields are not very concerned.
Since we cannot change the descending and ascending order of each column of the index, we can make the index fields descending/ascending in the same order by the time we write the order by. Finally, the 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; 

Run it. 0.029 S. Get.

References

• 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

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