found, old table format sorry guys 2015-08-21 14:56 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
in other words using "having" is faster:
explain select lote_spa,mov_id from ( SELECT lote_spa,mov_id,oe_tipo FROM est_mov WHERE unidade_id=1001 AND lote_tipo='v' AND lote_spa IN (1150150.0,1150532.0,1152187.0,1152361.0,1155813.0,1156244.0,1156654.0) AND item_id=67 AND item_id_red=1423 having oe_tipo='oe' ) as t
what i'm doing wrong?
2015-08-21 14:54 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
Hi guys I'm running two queries:
first:
explain SELECT lote_spa,mov_id FROM est_mov WHERE unidade_id=1001 AND lote_tipo='v' AND lote_spa IN (1150150.0,1150532.0,1152187.0,1152361.0,1155813.0,1156244.0,1156654.0) AND item_id=67 AND item_id_red=1423
it use primary key and key_len =16
but including a new = element [AND oe_tipo='oe' ]:
explain SELECT lote_spa,mov_id FROM est_mov WHERE unidade_id=1001 AND lote_tipo='v' AND lote_spa IN (1150150.0,1150532.0,1152187.0,1152361.0,1155813.0,1156244.0,1156654.0) AND item_id=67 AND item_id_red=1423 AND oe_tipo='oe'
it use primary key but key_len=4 (near to a full table scan)
what's wrong?
-- Roberto Spadim
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle