How can I optimize this query to get faster result?

Question

How can I optimize this query to get result from it faster?

update TblPartes inner join (select TP.codigo1, COUNT(TE.id_codigo) AS COUNT FROM TblPartes TP left JOIN TblExistencias as TE ON TP.id=TE.id_codigo where TE.vendido = 0 or TE.vendido is null or TE.vendido = 0 and TP.id_ubicacion is null or TE.vendido is null and TP.id_ubicacion is null GROUP BY TP.id ) as E on TblPartes.codigo1=E.codigo1 set stock = E.count  where TblPartes.codigo1=E.codigo1

Answer 1

The issue boils down to the speed of this, correct?

    SELECT  TP.codigo1, COUNT(TE.id_codigo) AS COUNT
            FROM  TblPartes TP
            left JOIN  TblExistencias as TE  ON TP.id=TE.id_codigo
            where  TE.vendido = 0
              or  TE.vendido is null
              or  TE.vendido = 0
              and  TP.id_ubicacion is null
              or  TE.vendido is null
              and  TP.id_ubicacion is null
            GROUP BY  TP.id 

Suggestions:

• Decide whether to use 0 versus NULL. That way the query won't have to test for both. (This may require changes to the data and how the data is loaded.)
• Note that COUNT(x) avoids counting when x is NULL; is that what you intended?
• You can probably avoid the GROUP BY (which may be a big factor in speed) by turning the LEFT JOIN into EXISTS(SELECT ...).
• The UPDATE is testing for TblPartes.codigo1=E.codigo1 twice`.
• Add parentheses were appropriate in that long boolean expression -- and and or may not be combining the way you expect!

Please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

These indexes may help:

TP:  INDEX(id_ubicacion, id,  codigo1)
TE:  INDEX(vendido, id_codigo)