List:MySQL on Win32« Previous MessageNext Message »
From:mos Date:November 6 2009 3:53pm
Subject:Re: Help on query performance
View as plain text  
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

Thread
Help on query performanceJorge Bastos5 Nov
  • RE: Help on query performanceSandeep Tamhankar6 Nov
    • SV: Help on query performanceJesper Palmqvist  [Talarforum i Skandinavien AB]6 Nov
      • SV: Help on query performanceJesper Palmqvist  [Talarforum i Skandinavien AB]6 Nov
        • RE: Help on query performanceJorge Bastos6 Nov
        • RE: Help on query performanceSandeep Tamhankar6 Nov
          • RE: Help on query performancemos7 Nov
  • Re: Help on query performancemos6 Nov
    • RE: Help on query performanceJorge Bastos6 Nov
  • Re: Help on query performanceuwe6 Nov