Shulou(Shulou.com)06/01 Report--

Simple query, return the same, use charge_id to associate, only 0.5s, but if you use order_id, it takes 18s! Why?

When using order_id, the execution plan uses Using join buffer (Block Nested Loop); the reason is ascertained: the order_id character set in order_forInit is utf8, and the order_id character set in order_item_forInit is utf8mb4, different character sets cause two join when you can't use the index, there will be "Using join buffer (Block Nested Loop)". Change the order_id character set in order_forInit to utf8mb4, and there will be no performance problem! Using join buffer (Block Nested Loop) will not appear

Explain

Select count (*) from

Order_forInit a

Order_item_forInit c

Product d

WHERE

-- a.order_id = c.order_id

A.charge_id = c.charge_id

AND c.product_id = d. Product_id

Appendix:

The difference between the mysql character set utf8 and utf8mb4: https://blog.csdn.net/qq_37054881/article/details/90023611

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.