Description:
hi guys i was checking some explain to speed up some queries
i got a interesting question...
check the where of this query:
SELECT SQL_BUFFER_RESULT SQL_NO_CACHE DISTINCT
some fields...
FROM est_mov AS a, estoque_itens AS b, cfop_itens AS c , organograma AS f , organograma AS d
WHERE
a.unidade_id=1000 AND
a.oe_tipo="op" AND
a.item_id=50 AND
a.lote_rendimento_data="2011-03-24" AND a.lote_rendimento_numero>=0 AND a.lote_rendimento_numero<=9999 AND
a.rendimento='Y' AND
d.plano_conta_id=a.estoque_entrada_org AND
d.plano_conta_id_red=a.estoque_entrada_org_red AND
f.plano_conta_id_red=a.ponto_movimentacao AND f.plano_conta_id=1 AND f.plano_conta_numero LIKE "3.05.01.%" AND
b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca = "96500" AND
c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.100"
when i use
b.codigo_busca = "96500"
or this:
c.plano_conta_numero = "0.100"
i see:
Using temporary, at EXTRA field of explain
when i use:
b.codigo_busca LIKE "96500"
or this:
c.plano_conta_numero LIKE "0.100"
i see:
Using where
or
Using where; Using temporary
or
Using where; Using join buffer
the question is
like without % or _, should execute like "=" operator, i´m right?
maybe this could make some queries (optimizer) faster, since optimizer change the rule of optimization
How to repeat:
:/ i don't know maybe i should send all tables?
it's very big, est_mov~=10gb, estoque_itens~=1Mb,cfop~=1mb,organograma~=1mb
could some create tables help?
Suggested fix:
maybe when no willcard "%","_" is found in LIKE operator, it could be changed to "=" operator
