List:MySQL on Win32« Previous MessageNext Message »
From:uwe Date:November 6 2009 10:32pm
Subject:Re: Help on query performance
View as plain text  
jorge,

remmebr that you can use the "explain" syntax in mysql to analyse which
indexes are used by your query. if you have your indexes right and
queries are still too slow, then you could try using e.g. infobright as
a column based database engine (mysql). it may be faster and it is easy
to use as mysql.

rgds,

uwe

Am Donnerstag, den 05.11.2009, 22:20 +0000 schrieb Jorge Bastos:
> 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,
> 
>  
> 
> the table struct is:
> 
>  
> 
> CREATE TABLE mov_stock (
> 
>   emp varchar(2) NOT NULL,
> 
>   ano int(4) NOT NULL,
> 
>   stat int(2) NOT NULL DEFAULT '0',
> 
>   datadoc date NOT NULL,
> 
>   tipodoc varchar(3) NOT NULL DEFAULT '',
> 
>   serie int(6) NOT NULL DEFAULT '0',
> 
>   numero int(7) NOT NULL DEFAULT '0',
> 
>   nrlinha int(11) NOT NULL DEFAULT '0',
> 
>   operstock int(3) NOT NULL,
> 
>   arm_cod int(6) NOT NULL DEFAULT '0',
> 
>   cod_art varchar(30) NOT NULL DEFAULT '0',
> 
>   valor_unitario decimal(16,6) NOT NULL,
> 
>   valor_mov decimal(16,6) NOT NULL,
> 
>   nrlinha_pmc bigint(16) NOT NULL DEFAULT '0',
> 
>   qt_actual decimal(16,6) NOT NULL,
> 
>   pmc_actual decimal(16,6) NOT NULL,
> 
>   entrada decimal(16,6) NOT NULL DEFAULT '0.000000',
> 
>   saida decimal(16,6) NOT NULL DEFAULT '0.000000',
> 
>   PRIMARY KEY (emp,ano,stat,tipodoc,serie,numero,nrlinha),
> 
>   KEY index_nrlinhapmc (nrlinha_pmc),
> 
>   KEY index_entrada (entrada),
> 
>   KEY index_saida (saida)
> 
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
> 


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