mysql 语句查询 优化

Question

order表字段：order_id,product_id查询购买产品A的订单同时会购买哪些产品SELECTprodcut_id,COUNT(*)ASnumsFROMorderLEFTJOIN(SELECTorder_idFROMorderWHEREproduct_id=100)ASorder_bONorder_b.order_id=order.order_idWHEREorder_b.order_idISNOTNULLGROUPBYproduct_idORDERBYnumsDESCoder_id,prodcut_id已做索引请问如何优化以上查询语句？

Answer 1

用JOIN替换掉LEFTJOIN：
SELECTprodcut_id,COUNT(*)ASnums
FROMorder
JOIN(
SELECTorder_id
FROMorder
WHEREproduct_id=100
)ASorder_bONorder_b.order_id=order.order_id
GROUPBYproduct_id
ORDERBYnumsDESC
                            

Answer 2

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