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

CodePudding user response：

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)