maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

found, old table format
sorry guys

2015-08-21 14:56 GMT-03:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:

> 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
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle