0

I have a select query that should return a result with about 200 rows from 3 tables:

  • wb_mod_deposit_out - 700.000 rows
  • wb_mod_deposit - 100.000 rows
  • from_generale_java - 1200 rows.

The query is:

SELECT 

    DATE(a.created) AS DATA_MANCANTE, a.stagione, 
    c.status, a.modello, a.commessa, a.qnt_prod, 
    a.fabrica, a.CODE, a.color, a.measure, 
    ROUND(a.qnt,2) AS qnt_not_enough, 
    a.dist_base, b.qnt AS qnt_dept, 
    DATE(a.created) AS DATA_PRELIEVO, 
    C.`DATA_PREV_INI_TAG`, C.`DATA_PREV_INI_CONF` 

FROM

 wb_mod_deposit_out a,

 (SELECT CODE, color, ROUND(SUM(qnt),2) AS qnt 
  FROM wb_mod_deposit 
  GROUP BY CODE, color
 ) b, 

 from_generale_java C 

WHERE
      a.TYPE = 2 
  AND a.code = b.code 
  AND a.color = b.color
  AND b.qnt <= 0
  AND A.modello = C.MODELLO
  AND A.COMMESSA = C.COMMESSA
  AND A.STAGIONE = C.STAGIONE;

It takes about 2 min to return the result.

I have found a way to check myself with EXTEND: https://www.slideshare.net/phpcodemonkey/mysql-explain-explained

  • It would help if you added the output of EXPLAIN SELECT ...; to the question and also the output of SHOW CREATE TABLE ...; for the 3 tables. You probably need some indexes. – ypercubeᵀᴹ Nov 30 '17 at 12:28
  • Also add the version of MySQL. – ypercubeᵀᴹ Nov 30 '17 at 12:29
  • Thanks to all - i was using wrong indexes. "EXPLAIN" helped me to figure out. – Valeriu Chirica Dec 7 '17 at 10:38
1

Move the qnt <= 0 inside as a HAVING (not WHERE).

Use JOIN ... ON ... instead of FROM a,b WHERE a...=b...

Add the following composite indexes:

a: INDEX(type, code, color)
b: INDEX(code, color, qnt)
c: INDEX(modello, commessa, stagione)
  • Thanks. The first thing i was doing wrong is that i didn't had indexes on my columns. – Valeriu Chirica Dec 7 '17 at 10:44
  • @ValeriuChirica - Indexes are very important for performance. Read my Cookbook for more tips on indexing. – Rick James Dec 25 '17 at 17:00
0

First thing I would try to make this faster, is to move the b.qnt <= 0 filter closer to the wb_mod_deposit table as a WHERE or HAVING filter:

SELECT
    DATE(a.created) AS DATA_MANCANTE, a.stagione,
    c.status, a.modello, a.commessa, a.qnt_prod,
    a.fabrica, a.CODE, a.color, a.measure,
    ROUND(a.qnt,2) AS qnt_not_enough,
    a.dist_base, b.qnt AS qnt_dept,
    DATE(a.created) AS DATA_PRELIEVO,
    C.`DATA_PREV_INI_TAG`, C.`DATA_PREV_INI_CONF`

FROM
    wb_mod_deposit_out a

   ,(SELECT CODE, color, ROUND(SUM(qnt),2) AS qnt
     FROM wb_mod_deposit
     WHERE qnt <= 0 --try filtering early here...(test this version!)
     GROUP BY CODE, color
     --HAVING ROUND(SUM(qnt),2) <= 0  -- ...or here.
    ) b

   ,from_generale_java c

WHERE
      a.TYPE = 2
  AND a.code  = b.code
  AND a.color = b.color
  AND a.modello  = c.MODELLO
  AND a.COMMESSA = c.COMMESSA
  AND a.STAGIONE = c.STAGIONE;
  • If b.qnt values to be SUMmed can be positive or negative, then this will pull anything with at least one negative or 0 qnt, even if the sum of all the qnts would be positive. I think you need to go with the HAVING (and probably HAVING ROUND(SUM(qnt),2) <= 0, since column aliases can't usually be used except in the ORDER BY clause of a query). – RDFozz Nov 30 '17 at 19:21
  • RDFozz, you're totally right about both points. I thought of the 1st one right at the end and added the HAVING clause just before I posted the answer, without testing. That'll teach me! Thanks, and post fixed! – Oreo Dec 1 '17 at 9:41

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.