If there is no suitable where filter condition, try to select the field after orderby to drive the table for query.

Background query statement SELECTo. orders_id, s. orders_status_name, ot. text, af. affiliate_idFROMordersoLEFTJOINorders_totalotON (o. orders_idot.orders_id) LEFTJOINaffilia

Background query statement SELECTo. orders_id, s. orders_status_name, ot. text, af. affiliate_idFROMordersoLEFTJOINorders_totalotON (o. orders_id = ot. orders_id) LEFTJOINaffilia

Background query statement

SELECT o. orders_id, s. orders_status_name, ot. text, af. affiliate_idFROM orders oLEFT JOIN orders_total ot ON (o. orders_id = ot. orders_id) left join affiliate_sales AS afs ON afs. affiliate_orders_id = o. orders_idLEFT JOIN affiliate_affiliate AS af ON af. affiliate_id = afs. affiliate_idLEFT JOIN orders_status s ON o. orders_status = s. orders_status_idwheres.w.age_id = '1' AND (ot. class = 'ot _ total' OR ot. orders_total_id is null) order by o. orders_id desc limit 0, 20

Some customers responded that a background query was very slow. They found the corresponding SQL through the program, as shown above!

Explain discovery

+ ---- + ------------- + ------- + -------- + ---------------------------- + Hour + --------- + hour + ------- + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows extra | + ---- + ------------- + ------- + -------- + upper + ---------------------------- + --------- + upper + ------- + upper + | 1 | SIMPLE | s | ALL | PRIMARY | NULL | 21 | Using where; using temporary; Using filesort | 1 | SIMPLE | o | ref | orders_status | 4 | banggood. s. orders_status_id | 31747 ||| 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | 4 | banggood. o. orders_id | 19 | Using where | 1 | SIMPLE | afs | ref | PRIMARY | 4 | banggood. o. orders_id | 11 | Using index | 1 | SIMPLE | af | eq_ref | PRIMARY | 4 | banggood. afs. affiliate_id | 1 | Using index | + ---- + ------------- + ------- + -------- + ---------------------------- + ------------------------ + --------- + certificate + ------- + certificate +

The s table is used as the driving table, and the s table is used as the full table scan. The o table uses the status field with very low selectivity as the index.

At first glance, we can see that the index usage is inappropriate!

We can see that there are no suitable driver conditions in the where condition of this statement. However, in order by, order by o. orders_id (orders_id is the primary key of the orders table) is found ). We can use this feature!

Use the orders_id index of the orders table!

The changes are as follows:

Explain select o. orders_id, s. orders_status_name, ot. text, af. affiliate_idFROM orders o force index (PRIMARY) left join orders_total ot ON (o. orders_id = ot. orders_id) left join affiliate_sales AS afs ON afs. affiliate_orders_id = o. orders_idLEFT JOIN affiliate_affiliate AS af ON af. affiliate_id = afs. affiliate_idLEFT JOIN orders_status s ON o. orders_status = s. orders_status_idwheres.w.age_id = '1' AND (ot. class = 'ot _ total' OR ot. orders_total_id is null) order by o. orders_id desc limit 0, 20; + ---- + ------------- + ------- + -------- + hour + --------- + hour + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + -------- + upper + -------------------------- + --------- + upper + ------ + ------------- + | 1 | SIMPLE | o | index | NULL | PRIMARY | 4 | NULL | 1 | 1 | SIMPLE | s | eq_ref | PRIMARY | 8 | banggood. o. orders_status, const | 1 | Using where | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | 4 | banggood. o. orders_id | 19 | Using where | 1 | SIMPLE | afs | ref | PRIMARY | 4 | banggood. o. orders_id | 11 | Using index | 1 | SIMPLE | af | eq_ref | PRIMARY | 4 | banggood. afs. affiliate_id | 1 | Using index | + ---- + --------------- + ------- + -------- + ---------------------------- + ---------------------- + --------- + ------ + ------------- +

Comparison of Two profiling;

The former:

+ Duration + ------------ + ----------- + ------------ + Duration + | Status | Duration | CPU_user | CPU_system | usage | percent | + percent + ------------ + ----------- + ------------ + percent + | starting | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | Waiting for query cache lock | 0.000006 | 0.000000 | 0.000000 | 0 | checking query cache for query | 0.000130 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | Opening tables | 0.000130 | 0.000000 | 0.000000 | 0 | 8 | System lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | Waiting for query cache lock | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 | init | 0.000057 | 0.000000 | 0.000000 | 0 | 0 | optimizing | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | statistics | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | 0 | | preparing | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | Creating tmp table | 0.000111 | 0.001000 | 0.000000 | 0 | executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 | Copying to tmp table | 3.541123 | 0.968852 | 2.357642 | 75800 | 0 | converting HEAP to MyISAM | 0.239566 | 0.038994 | 0.198969 | 0 | 262152 | Copying to tmp table on disk | 174.185144 | 13.864893 | 35.361625 | 2135152 | 2500280 | Sorting result | 20.923419 | 0.127980 | 3.017541 | 2770408 | Sending data | 27536 | 0.045078 | 0.000000 | 0.002999 | 0 | end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | removing tmp table | 0.881884 | 0.018997 | 0.160976 | 760 | 8 | end | 0.003960 | 0.000000 | 0.002000 | 448 | 0 | | query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | closing tables | 0.031745 | 0.000000 | 0.000999 | 936 | 0 | freeing items | 0.015499 | 0.000000 | 0.003000 | 808 | 0 | Waiting for query cache lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | freeing items | 0.000791 | 0.000000 | 0.000000 | 0 | 0 | Waiting for query cache lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | freeing items | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | storing result in query cache | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | 0 | logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | logging slow query | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | cleaning up | 0.000007 | 0.000000 | 0.000000 | | 0 | 0 | + ------------------------------ + ------------ + ------------- + ------------ + -------------- + ----------------- +

Various cpu and I/O losses are terrible! The maximum consumption is Copying to tmp table on disk.