2

I have a query that have this statement:

SELECT DISTINCT C.id_produto
  FROM movdiario C
 INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM
 INNER JOIN estoques B ON B.id_produto=C.id_produto 
                      AND B.id_empresa= 1
  LEFT JOIN ESTATISTICAS_PRODUTO E ON E.ID_PRODUTO = B.ID_PRODUTO 
                                  AND E.ID_EMPRESA = B.ID_EMPRESA
 WHERE C.empresa = 1 
   AND C.emissao BETWEEN ('2019-10-28') AND ('2019-12-12')
   AND C.tipo = 'V' 
   AND (C.situacao IS NULL OR c.situacao = '') 
   AND P.ATIVO = 'S'

When I use an interval between 2019-10-28 and 2019-12-12 the query run fast using the correct index.

enter image description here

But when I use the interval 2019-10-27 and 2019-12-12 the query runs slowly and I have the explanation as follow:

enter image description here

What's wrong?
I have indexes on fields c.empresa, c.emissao and have a composite index c.empresa,c.emissao.

1
  • How much recods are in table 1) totally 2) for the interval between 2019-10-27 and 2019-12-12? And show movdiario's DDL at least.
    – Akina
    Dec 12, 2019 at 13:09

1 Answer 1

1

The reason: If the range is large, it is faster to simply ignore the index and scan through the table. Note that using an index implies flipping back and forth between the index and the data.

 WHERE C.empresa = 1 
   AND C.emissao BETWEEN ('2019-10-28') AND ('2019-12-12')
   AND C.tipo = 'V' 

The optimal index is

INDEX(empresa, tipo,  -- in either order
      emissao)        -- then the 'range' column

It would help if you decided on either NULL or '' in the following. That way, that column could be included in the above index to make it even better.

   AND (C.situacao IS NULL OR c.situacao = '') 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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