How to optimize update in query efficiency by mysql update join
This article mainly shows you "mysql update join how to optimize update in query efficiency", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql update join how to optimize update in query efficiency" this article.
Database version: 5.6.16
Update in modifies the data, as a result, the execution time is too slow and there is no result.
The SQL statement and execution plan are as follows:
UPDATE erp_order_extra SET last_time=123123123123
WHERE order_id IN (SELECT eo.order_id FROM jiuxianweb.erp_order eo WHERE eo.channel_ordersn='18161116045333705180')
+-+ -+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+ -+
| | 1 | PRIMARY | erp_order_extra | index | NULL | PRIMARY | 4 | NULL | 7528568 | Using where; Using temporary |
| | 2 | DEPENDENT SUBQUERY | eo | unique_subquery | PRIMARY,channel_ordersn | PRIMARY | 4 | func | 1 | Using where |
+-+ -+
The index structure of the two tables is as follows:
Mysql > show index from erp_order_extra
+-+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-+ -+
| | erp_order_extra | 0 | PRIMARY | 1 | extra_id | A | 7528568 | NULL | NULL | | BTREE |
| | erp_order_extra | 1 | order_id | 1 | order_id | A | 7528568 | NULL | NULL | | BTREE |
| | erp_order_extra | 1 | action_id | 1 | action_id | A | 2 | NULL | NULL | YES | BTREE |
| | erp_order_extra | 1 | split_type | 1 | split_type | A | 4 | NULL | NULL | | BTREE |
+-+ -+
Mysql > show index from erp_order
+- +-+ +
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+- +-+ +
| | erp_order | 0 | PRIMARY | 1 | order_id | A | 8902636 | NULL | NULL | | BTREE |
| | erp_order | 0 | channel_ordersn | 1 | channel_ordersn | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 0 | channel_ordersn | 2 | channel_id | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | ordersn | 1 | order_sn | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | printBatch | 1 | printBatch | A | 2225659 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | indexes_order_status | 1 | order_status | A | 5324 | NULL | NULL | | BTREE |
| | erp_order | 1 | indexes_order_status | 2 | depart | A | 14198 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | indexes_order_status | 3 | channel_id | A | 53309 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | orderstatus | 1 | order_status | A | 18 | NULL | NULL | | BTREE |
| | erp_order | 1 | orderstatus | 2 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | addtime | 1 | add_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | consignee | 1 | consignee | A | 2967545 | NULL | NULL | | BTREE |
| | erp_order | 1 | consignee | 2 | verify_status | A | 2967545 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | consignee | 3 | add_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | channeluser | 1 | channel_id | A | 14131 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | channeluser | 2 | verify_status | A | 34912 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | channeluser | 3 | user_id | A | 4451318 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | mobile | 1 | mobile | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | extension_id | 1 | extension_id | A | 38707 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | self_id | 1 | self_id | A | 864 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | extension_code | 1 | extension_code | A | 3487 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | tel | 1 | tel | A | 2225659 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | order_amount | 1 | order_amount | A | 53955 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | invoice_no | 1 | invoice_no | A | 8902636 | NULL | NULL | | BTREE |
| | erp_order | 1 | standard_time | 1 | standard_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_time | 1 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | confirm_time | 1 | confirm_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | pay_time | 1 | pay_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | ware_id | 1 | ware_id | A | 110 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | admin_id | 1 | admin_id | A | 1936 | NULL | NULL | | BTREE |
| | erp_order | 1 | userid | 1 | user_id | A | 2967545 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_id | 1 | channel_id | A | 18131 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_id | 2 | shipping_id | A | 74188 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_id | 3 | user_id | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_id | 4 | verify_status | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | shipping_id | 5 | add_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | ind_channel_orderid_erp_order | 1 | channel_orderid | A | 4451318 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | index_erp_order_shipping_time | 1 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | ind_original_id_erp_order | 1 | original_id | A | 890263 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | idx_ChangeTime | 1 | ChangeTime | A | 45654 | NULL | NULL | | BTREE |
| | erp_order | 1 | restorestock_status | 1 | restorestock_status | A | 4 | NULL | NULL | | BTREE |
| | erp_order | 1 | index_temp | 1 | order_status | A | 3697 | NULL | NULL | | BTREE |
| | erp_order | 1 | index_temp | 2 | is_range | A | 5915 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | index_temp | 3 | islock | A | 7394 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | index_temp | 4 | verify_status | A | 9614 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | index_temp | 5 | invoice_no | A | 8902636 | NULL | NULL | | BTREE |
| | erp_order | 1 | index_ShopSn | 1 | ShopSn | A | 26 | NULL | NULL | YES | BTREE |
| | erp_order | 1 | index_SellerSn | 1 | SellerSn | A | 24 | NULL | NULL | YES | BTREE |
+- +-+ +
Optimize the query efficiency of update in through update join:
Original SQL:
UPDATE erp_order_extra SET last_time=123123123123
WHERE order_id IN (SELECT eo.order_id FROM jiuxianweb.erp_order eo WHERE eo.channel_ordersn='18161116045333705180')
JOIN SQL:
Update erp_order_extra an inner join erp_order b on a.order_id=b.order_id set last_time=123123123123 where b. Channelmakers ordersnails 18161116045333705180'
Execute the plan:
+-+-- +-+ -- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-- +-+ -- +
| | 1 | SIMPLE | b | ref | PRIMARY,channel_ordersn | channel_ordersn | 153 | const | 1 | Using where; Using index |
| | 1 | SIMPLE | a | ref | order_id | order_id | 4 | jiuxianweb.b.order_id | 1 | NULL |
+-+-- +-+ -- +
These are all the contents of the article "how mysql update join optimizes update in query efficiency". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
