List:MySQL on Win32« Previous MessageNext Message »
From:Jesper Palmqvist  [Talarforum i Skandinavien AB] Date:November 6 2009 7:12am
Subject:SV: Help on query performance
View as plain text  
Setting non-unique composite index on (emp, ano, arm_cod, cod_art) should make the query
real fast. Indexes are most important for finding what rows to look in. Ie in this case -
the parameters within WHERE. Indexing columns that are not used in WHERE syntaxes or in
JOIN syntaxes are rarely useful, only space consuming. Indexeing also somewhat slows down
UPDATEs and INSERTs. Normally not a hazzle unless a lot of fields are often updated and
inserted.

You ought to be fine with only non-unique composite index on (emp, ano, arm_cod, cod_art)

/Jesper


-----Ursprungligt meddelande-----
Från: Sandeep Tamhankar [mailto:sandman@stripped] 
Skickat: den 6 november 2009 00:06
Till: Jorge Bastos; win32@stripped
Ämne: RE: Help on query performance

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;


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/win32?unsub=1


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