mysql 语句查询 优化

Question

order表
字段：order_id,product_id
查询购买产品A的订单同时会购买哪些产品
SELECTprodcut_id,COUNT(*)ASnums
FROMorder
LEFTJOIN(
SELECTorder_id
FROMorder
WHEREproduct_id=100
)ASorder_bONorder_b.order_id=order.order_id
WHEREorder_b.order_idISNOTNULL
GROUPBYproduct_id
ORDERBYnumsDESC
oder_id,prodcut_id已做索引
请问如何优化以上查询语句？

Answer 1

用JOIN替换掉LEFTJOIN：SELECTprodcut_id,COUNT(*)ASnumsFROMorderJOIN(SELECTorder_idFROMorderWHEREproduct_id=100)ASorder_bONorder_b.order_id=order.order_idGROUPBYproduct_idORDERBYnumsDESC

Answer 2

oder_id,prodcut_id已做索引索引是两索引分别在order_id,prodcut_id上？还是一个索引在（order_id,prodcut_id）?Query1:selectproduct_id,count(product_id)fromorderWHEREorder_idin(Selectorder_idFROMorderWHEREproduct_id=100)groupbyproduct_id;Query2:selecta.product_id,count(a.product_id)fromorderainnerjoinorderbona.order_id=b.order_idandb.product_id=100groupbyproduct_id;Query2isbetter.