Query runs for too long 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 &lt;= 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
Move the qnt &lt;= 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)