MySQL wrong use of index when a date interval is big

Question

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.

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

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

Answer 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 = '')