List:MySQL on Win32« Previous MessageNext Message »
From:Sandeep Tamhankar Date:November 5 2009 11:06pm
Subject:RE: Help on query performance
View as plain text  
I'd start with creating a non-unique composite index on (emp, ano, arm_cod, cod_art). 
That should speed up the filtering.  It might help to add entrada and saida to the index
as well, so that MySQL never needs to look at the actual table rows.  I'm not sure how
smart MySQL is about avoiding accessing table rows if all information is available in the
index.

Good luck!

-Sandeep

-----------------------------------------------------------
Sandeep Tamhankar
Senior Software Engineer - Technical Lead
Electric Cloud, Inc.
sandman@stripped

-----Original Message-----
From: Jorge Bastos [mailto:mysql.jorge@stripped] 
Sent: Thursday, November 05, 2009 2:20 PM
To: win32@stripped
Subject: Help on query performance

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