首頁猿問 mysql 語句查詢優(yōu)化

mysql 語句查詢優(yōu)化

JavaScript

慕勒3428872 2019-04-19 16:13:12

order表字段：order_id,product_id查詢購買產(chǎn)品A的訂單同時會購買哪些產(chǎn)品SELECTprodcut_id,COUNT(*)ASnumsFROMorderLEFTJOIN(SELECTorder_idFROMorderWHEREproduct_id=100)ASorder_bONorder_b.order_id=order.order_idWHEREorder_b.order_idISNOTNULLGROUPBYproduct_idORDERBYnumsDESCoder_id,prodcut_id已做索引請問如何優(yōu)化以上查詢語句？

查看完整描述

2 回答

FFIVE

TA貢獻1797條經(jīng)驗獲得超6個贊

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

反對回復(fù) 2019-04-19

小怪獸愛吃肉

TA貢獻1852條經(jīng)驗獲得超1個贊

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.