← Back to team overview

maria-discuss team mailing list archive

Re: problem with optimizer

 

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@xxxxxxxxxxxxx>:

> 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

Follow ups

References