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