List:MySQL on Win32« Previous MessageNext Message »
From:mos Date:November 7 2009 10:55pm
Subject:RE: Help on query performance
View as plain text  
At 11:39 AM 11/6/2009, Sandeep Tamhankar wrote:
>I have a few questions, based on your response:
>
>1. A table can have only one primary key, and thus one primary key 
>index.  Any other index is either a unique index or non-unique index.  So 
>that means
>"PRIMARY KEY (emp,ano,stat,tipodoc,serie,numero,nrlinha)" is a composite 
>primary key with one primary key index, doesn't it?

Correct. But primary keys are usually an Integer like an AutoInc because 
they are faster to join to other tables with. You can have a compound index 
as a primary key, there is no rule against it, but it will mean you need 
more fields to uniquely identify a record. If you are doing table joins 
this will be slower than using a simple integer column.

>You asked Jorge why he was using many primary key indices, and I don't see 
>how he could be doing that.  Please clarify.

I couldn't read his SQL structure because it was just a blob of unformatted 
text. I was implying that the more indexes a table has, the slower the 
inserts will become, especially if the indexes are unique.


>2. Does MySQL do index joins?

You mean joins to other tables? Sure. But I've found MySQL 5.1 doesn't 
always use the best index. 5.1 was rushed out and I'm forced to check the 
index it is using with Explain and often I have to use Force Index(myindex) 
to get it to use the proper index. See 
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

>Here we have a primary key index with several columns more than what we 
>need for this query.

And that makes for a larger index file and requires more memory to contain 
the entire index. It is likely defined that way to allow the query to 
access only the index to satisfy the Where clause which is fine.

>And we have your non-unique index on (arm_cod, cod_art).  Will MySQL's 
>query optimizer apply the relevant predicate to each index, then join both 
>together on row_id to get the final set of rows that need to be returned?

Huh? Not sure I follow. MySQL will read the index to satisfy the Where 
clause. I always like to have the most unique column first in the compound 
index to make index searching faster. (assuming B-Tree index). You can also 
try a Hash index to see if that is any faster. That's why I recommended a 
different index column order.

>I've been under the impression that when a query is executed, MySQL will 
>use one index per table in the FROM clause.

Usually but not in all cases. With MySQL 5.1 it can supposedly use more 
than 1 index on a query. (I'm not sure this is working-I've never seen it 
use more than 1 index but stranger things have happened. :-).

>So in this case (where we're only querying one table), it would have to 
>choose which index will reduce the result-set the most and use that one, 
>and then do more reduction by walking the rows of the table itself.

Correct. Which is why I think all of the columns from the Where clause was 
put into the compound index, to eliminate going to the data file to satisfy 
the Where clause. This *may* speed things up but further benchmarking is 
always warranted to see if it actually runs faster.

Remember, EXPLAIN is your friend. Always benchmark and retest to make sure 
MySQL is working to its best efficiency. You may want to execute a Flush 
Tables or Reset Query Cache when retesting to make sure the rows aren't 
already loaded into memory.

Mike


>Thanks!
>
>-Sandeep
>
>-----------------------------------------------------------
>Sandeep Tamhankar
>Senior Software Engineer - Technical Lead
>Electric Cloud, Inc.
>sandman@stripped
>
>-----Original Message-----
>From: Jesper Palmqvist [Talarforum i Skandinavien AB] 
>[mailto:jesper.palmqvist@stripped]
>Sent: Thursday, November 05, 2009 11:22 PM
>To: win32@stripped
>Subject: SV: Help on query performance
>
>Sorry - looked a bit further into you table syntax
>
>Since both emp and ano are primary keys already - only set non-unique 
>composite index on (arm_cod, cod_art)
>
>Maybe you could enlighten me by explaing why using many primary indexes 
>instead of on primary and several unique composite indexes
>
>
>-----Ursprungligt meddelande-----
>Från: Jesper Palmqvist [Talarforum i Skandinavien AB] 
>[mailto:jesper.palmqvist@stripped]
>Skickat: den 6 november 2009 08:13
>Till: win32@stripped
>Ämne: SV: Help on query performance
>
>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
>
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To 
>unsubscribe: 
>http://lists.mysql.com/win32?unsub=1
>
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To 
>unsubscribe:    http://lists.mysql.com/win32?unsub=1
>
>
>--
>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