At 04:20 PM 11/5/2009, Jorge Bastos wrote:
>Howdy people,
>
>
>
>I'd like to ask help on getting better performance on a query.
>
>The table has about 150.000 records.
>
>
>
>The query is:
>
>
>
>select sum(entrada)-sum(saida) from mov_stock where emp='01' and ano='2009'
>and arm_cod='2' and cod_art='2020051'
>
>
>
>this query takes 1.20 sec's. I need to change this to 0. Something,
>something below 0.50 secs is good!
>
>What can be done here, or what am I missing that is breaking performance?
>
>
>
>Thanks,
>
>Jorge,
Jorge,
In addition to the other poster's recommendation on making a
non-unique compound index, if this is a read-only table then you can get
faster performance by copying it to a memory table using:
create table mem_mov_stock engine=memory select * from mov_stock;
alter table mem_mov_stock add index ix_Main( cod_art,arm_cod,emp,ano);
This will get the speeds down quite a bit.
Mike
