Do not casually distinct and orderby

Some customers responded to the slow query of website background orders. The program obtained sqlexplainexplainSELECTDISTINCT (o. orders_id), o. oa_order_id, customers_email_address, o. order_type

Some customers responded to the slow query of website background orders. The program obtained sqlexplainexplainSELECTDISTINCT (o. orders_id), o. oa_order_id, customers_email_address, o. order_type

Some customers responded to the slow query of website background orders and obtained the related SQL through the program.

Explain

Explain select distinct (o. orders_id), o. oa_order_id, customers_email_address, o. order_type, ot. text AS total_value, o. track_number, o. date_purchased, o. orders_status, o. specialOperate, o. isSpecialParent, o. pay_ip, o. supply_id, o. products_center_id, o. split_code, o. is_import, o. shipDays, o. delivery_country, o. use_coupon, o. payment_method FROM orders AS o left join orders_total AS ot ON ot. orders_id = o. orders_id AND ot. class = 'ot _ total' WHERE 1 AND o. is_delete = 0 AND o. date_purchased> = '2017-09-30 10:00:00 'AND (o. specialOperate = 0 OR o. isSpecialParent = 1) order by date_purchased DESC, 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 | range | date_purchased | 9 | NULL | 606632 | Using where; using temporary; Using filesort | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id, class | idx_orders_total_orders_id | 4 | banggood. o. orders_id | 19 | + ---- + ------------- + ------- + response + -------------------------- + --------- + ---------------------- + -------- + rows + 2 rows in set (0.05 sec)

The index is found to be running normally, and the execution status is Copying to tmp table on disk. The execution time exceeds 50 s.

Using profiling, we found that Copying to tmp table on disk occupies most of the performance.

Check the statement carefully and discuss it with developers. We found that in distinct and order by date_purchased DESC, in orders_id DESC, the distinct keyword can be omitted, and order by date_purchased DESC, orders_id DESC can remove the following orders_id desc (developers do not understand the sorting of multiple fields ).

After removal, explain again

Mysql> EXPLAIN-> SELECT o. orders_id, o. oa_order_id, customers_email_address, o. order_type, ot. text AS total_value, o. track_number, o. date_purchased, o. orders_status, o. specialOperate, o. isSpecialParent, o. pay_ip, o. supply_id, o. products_center_id, o. split_code, o. is_import, o. shipDays, o. delivery_country, o. use_coupon, o. payment_method FROM orders AS o left join orders_total AS ot ON ot. orders_id = o. orders_id AND ot. class = 'ot _ total' WHERE 1 AND o. is_delete = 0 AND o. date_purchased> = '2017-09-30 10:00:00 'AND (o. specialOperate = 0 OR o. isSpecialParent = 1)-> order by date_purchased desc limit 0, 20; + ---- + ------------- + ------- + hour + --------- + hour + -------- + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + upper + --------- + ------------------------ + -------- + ------------- + | 1 | SIMPLE | o | range | date_purchased | 9 | NULL | 606632 | Using where | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id, class | idx_orders_total_orders_id | 4 | banggood. o. orders_id | 19 | + ---- + ------------- + ------- + response + -------------------------- + --------- + ---------------------- + -------- + ------------- + 2 rows in set (0.01 sec)

The index usage remains unchanged, but the following profiling shows that the result is instantaneous and the execution time is less than 0.003 s, and the Copying to tmp table on disk status is no longer available.

Conclusion: 1. because distinct keywords need to de-duplicate the result set, if there is no duplication, there is no need to add de-duplicate keywords, the above example result set has nearly one million, there are more de-duplicated fields, the sorting in tmp_table_size and sort_buffer_size is not enough, so copying the result set to the disk seriously affects the speed.

2. order by a, B developers like to use similar statements, although it has little effect on Functions

This article is from the "original" blog. Please keep this source