選擇查詢耗時過長 MYSQL 5.6.17

我有以下選擇，但不明白為什麼要花大約一個小時才能給我結果。

SELECT
(CASE
   WHEN a.ACODE='000A' THEN c.datfinprel 
   WHEN a.ACODE='000T' THEN c.datiniprel 
   WHEN a.ACODE='000S' && a.ACODETP=1 THEN c.datfinprel 
   WHEN a.ACODE='000S' && a.ACODETP=2 THEN c.datiniprel 
END) AS DATA_PREL,

A.`date_doc`, DATE(A.`created`) AS created, A.`stagione`, A.`fabbisogno`, A.`modello`, A.`commessa`,A.`code`, A.`title`, A.`color`, A.`measure`, A.`alt_pz`, A.`dist_base`, A.`qnt`,B.price, A.`cause`
FROM 
   wb_mod_deposit_out A, wb_mod_deposit B, from_generale_java C
WHERE 
   A.`stagione` IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')
AND B.ID=A.`deposit_id` 
AND a.`code`=B.`code` 
AND a.`color`=B.`color`
AND a.`stagione`=c.`stagione` 
AND a.`modello`=c.`modello` 
AND TRIM(LEADING '0' FROM a.`commessa`) = TRIM(LEADING '0' FROM c.`commessa`)
AND a.`type`=1;

擴展解釋：

id  select_type   table               type    possible_keys                                   key           key_len  ref                    rows  filtered  Extra                               
1  SIMPLE        C                   index   stagione,generale                               generale      123      (NULL)                 3729    100.00  Using where; Using index            
1  SIMPLE        A                   ref     stagione,test_OUT,CODE+COLOR,modello+comm+stag  stagione      153      csm_db.C.stagione       261    100.00  Using index condition; Using where  
1  SIMPLE        <subquery2>         eq_ref  <auto_key>                                      <auto_key>    153      csm_db.A.stagione         1    100.00  (NULL)                              
1  SIMPLE        B                   eq_ref  PRIMARY,test_depo                               PRIMARY       4        csm_db.A.deposit_id       1    100.00  Using where                         
2  MATERIALIZED  wb_mod_deposit_out  range   stagione,modello+comm+stag,date_created         date_created  6        (NULL)                15245    100.00  Using index condition; Using MRR    

請使用JOIN .. ON語法而不是表的 commalist（加WHERE子句。

使用JOIN（“派生”表）而不是IN ( SELECT ... )

wb_mod_deposit_out: INDEX(CREATED, STAGIONE)
a:  INDEX(stagione, type)     -- in either order
b:  INDEX(code, color, id)    -- in any order
c:  INDEX(stagione, modello)  -- in either order

如果id是PRIMARY KEYof b，為什麼還要過濾codeand color？

盡量遠離需要TRIM；它禁止在 上使用任何索引commessa。

的數據類型是什麼a.created？？如果是DATE，那麼DATE(..)是不必要的。如果是DATETIME，則BETWEEN跨度為 6 天加 1 秒；那是你想要的嗎？無論數據類型如何，這都有效：

WHERE created >= '2017-12-11'
     created  < '2017-12-11' + INTERVAL 7 DAY

引用自：https://dba.stackexchange.com/questions/193490