Note: Use BTREE to comindex ASC/DESC of each field to optimize order by query efficiency. btreeasc

Note: Use BTREE to comindex ASC/DESC of each field to optimize order by query efficiency. btreeasc
The tbl_direct_pos_201506 table has 1.9 million data, DDL:

Create table 'tbl _ direct_pos_201506 '('acq _ ins_code' char (13) not null default ''comment' mechanism Code', 'trace _ num' char (6) not null default ''comment' trace NO. ', 'Trans _ datetime' char (10) not null default ''comment' transaction time', 'process _ flag' char (1) default null comment 'processing identifies', 'Rev _ flag' char (1) default null comment 'receiving identifies', 'before _ trans_code 'char (3) default null comment 'transaction type', 'Trans _ amt 'decimal (15,3) default null comment' transaction amount', 'acct _ num' char (21) default null comment 'Card No. ', 'mer _ type' char (4) default null comment 'Merchant type', 'recv _ ins_code' char (13) default null comment 'issuing line Code', 'retrivl _ ref_num 'char (12) default null comment' search reference number', 'resp _ auth_code 'char (6) default null comment 'authorization Code', 'resp _ Code' char (2) default null comment 'response', 'TERM _ id' char (8) default null comment 'terminal Code', 'mer _ Code' char (15) default null comment 'Merchant Code', 'mer _ addr_name 'char (40) default null comment 'Merchant Name and address. The first 25 bytes are the name, followed by the address', and 'self _ define 'varchar (300) default null comment' 259th bytes are card type ', 'sys _ date' char (8) not null default ''comment' transaction date', 'sa _ sav2' varchar (300) default null comment' 243rd bytes are DCC identifiers ', 'rec _ create_time 'datetime default null comment' online warehouse receiving time', 'rec _ update_time 'datetime default null comment' last modification time', primary key ('sys _ date ', 'Trans _ datetime', 'acq _ ins_code ', 'trace _ num'), KEY 'idx _ direct_pos_create_time' ('rec _ create_time ')) ENGINE = MyISAM default charset = utf8 COMMENT = 'transaction month table template ';

A slow query log for this table is as follows:
# Time: 150701 15:45:28
# User @ Host: 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, 12) 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, 1) when 1 then 'debit card 'when 2 then'
When 3 then 'quasi-loan quota' when 4 then' private prepayment 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 'lycra'
When locate ('cup ', t. sa_sav2)> 0 then 'unionpay 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 '20160301' and '20160301'
Order
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 statement 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 this shows that our Query Needs to be optimized: File Sorting uses the corresponding Sorting Algorithm to sort the obtained data in the memory.
The primary key index and non-primary key index of the MyISAM storage engine differ little, but the index key of the primary key index is a unique and non-empty key. The default index of MyISAM is B-TREE. That is to say, the primary key here is equivalent to a common B-TREE.
This SQL statement contains a where field and four order by fields in the primary key, and the order by order fully complies with the leftmost prefix principle. Why does it require filesort?
MySql index creation manual says:
The index column definition can follow ASC or DESC. These keywords allow future extensions to specify the storage of index values in ascending or descending order. This syntax is parsed but ignored. Index columns are listed in ascending order by default.-- In other words, if you write it, no error is reported, but you write it in white.
In this case, our primary keys do not play a sorting role, because our primary keys are stored in the primary key field asc, and desc and asc in order by (asc by default) are mixed. To verify this statement, we replace this order by with the asc that is consistent with the primary key:

Select substr (t. acq_ins_code, 3) merAcqInsCode, t. mer_code, t. term_id, substr (t. mer_addr_name, 1, 12) 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 1 then 'debit card 'when 2 then' credit card 'when 3 then' quasi-credit card 'when 4 then' private prepayment 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 'lycra 'when locate ('cup', t. sa_sav2)> 0 then 'unionpay domestic 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 '000000' and '000000' order by t. sys_date, t. trans_datetime, t. acq_ins_code, t. trace_numlimit 0, 20;

Execution time: 0.023 s.
The results were unsatisfactory. View the 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 have used the primary key index, and Using filesort is gone.
Now that the problem is identified, the next thing seems to be a process.
Asked about the business, the sys_date and trans_datetime fields in the paging results must be sorted in descending order, and the other two fields are not very concerned.
Since we cannot change the descending and ascending order of each column of the index (the default value is ascending), we can make the descending/ascending order of each field of the index consistent when writing order. The final SQL statement is:

Select substr (t. acq_ins_code, 3) merAcqInsCode, t. mer_code, t. term_id, substr (t. mer_addr_name, 1, 12) 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 1 then 'debit card 'when 2 then' credit card 'when 3 then' quasi-credit card 'when 4 then' private prepayment 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 'lycra 'when locate ('cup', t. sa_sav2)> 0 then 'unionpay domestic 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 '000000' and '000000' order by t. sys_date desc, t. trans_datetime desc, t. acq_ins_code desc, t. trace_num desc limit 0, 20;

It takes 0.029 seconds to complete the execution.

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.