MySQL 5.6.17 – How to Optimize Select Query Performance

explainMySQLperformancequery-performanceselect

I have the below select and can't understand why it's taking about an hour to give me the result.

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;

The extended explain:

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

Best Answer

Please use the JOIN .. ON syntax instead of a commalist of tables (plus WHERE clauses.

Use JOIN (a 'derived' table) instead of 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

If id is the PRIMARY KEY of b, why do you also filter on code and color?

Try to get away from needing TRIM; it inhibits using any index on commessa.

What is the datatype of a.created?? If it is DATE, then DATE(..) is unnecessary. If it is DATETIME, then the BETWEEN spans 6 days plus 1 second; is that what you wanted? This works regardless of the datatype:

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