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

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

JavaScript

肥皂起泡泡 2019-04-21 20:38:09

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 回答

動漫人物

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

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

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

慕容森

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

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.