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

    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 

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`
    wb_mod_deposit_out A, wb_mod_deposit B, from_generale_java C
    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    

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
| improve this answer | |
  • the column CREATED has datatype: DATETIME, and unfortunately the interval is not always 7 days. I corrected the indexes and now it is giving the result in 3 sec. Thanks. – Valeriu Chirica Dec 25 '17 at 6:33
  • @ValeriuChirica - Change the 7 to whatever is needed. – Rick James Dec 25 '17 at 16:56
  1. You could try doing your Case selects as a cte.
  2. The line: A.'stagione' IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')

will also be a memory hog. Do you have an index on your CREATED column?

  1. Also, if you can find a way to take out TRIM(LEADING '0' FROM a.'commessa') = TRIM(LEADING '0' FROM c.'commessa') - maybe do it in an update statement or a third cte.
| improve this answer | |
  • 2. I have an index on column CREATED (type DATETIME) – Valeriu Chirica Dec 20 '17 at 7:11
  • @ValeriuChirica - with the 2-column INDEX(created, stagione), the index will be "covering", hence more efficient. – Rick James Dec 25 '17 at 16:53
  • CTEs are not available in MySQL until version 8.0. – Rick James Dec 25 '17 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.