List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 11 1999 1:44pm
Subject:Humbly looking for help in optimizing a query
View as plain text  
>>>>> "Richard" == Richard Dice <rdice@stripped> writes:

Richard> Hello, MySQL-ers...
Richard> I was hoping that some kind soul out there could help me with 
Richard> some query optimization.

Richard> I've included a bunch of diagnostic info below.  If there's anything
Richard> else I should be providing, please let me know!

Richard> tblIbmast  ~ 220,000 rows
Richard> tblUsage   ~ 130,000 rows
Richard> tblSlmmast ~   2,000 rows

Richard> The explain information and the query follow shortly.  Note that this 
Richard> _is_ the query I want -- it's not like I'm doing some kind of weird
Richard> cartesian product kind of thing without wanting to.  The results 
Richard> returned tabulate correctly as checked against an independent 
Richard> source.

Richard> Right now, on a Pentium 2 @ 450MHz w/320mb (RH Linux 5.2 w/Kernel 2.0.36), 
Richard> this query is taking ~ 0.75 seconds.  On a Pentium @ 150 MHz w/128mb, the 
Richard> query takes ~ 4.5 seconds.  These figures strike me as being unintuitively 
Richard> long -- there isn't that much logic going on inside the "select" statement, 
Richard> and the "explain" seems to be telling me that it'll only be looping over 2030
Richard> rows.  Surely this kind of computational horsepower could chew through 2030 
Richard> rows in faster than 0.75 (or 4.5) seconds.



Without looking too long on your query, I just wanted to point out
that it's the hard disk that it's usually the limiting factor.

For example; In your case MySQL may have to do 203*11*2 = 4466 disk
seeks. If a disk seeks takes 0.020 seconds, then this would take:
8.9 seconds.  Normally some disk seeks will be done at the same rows
and in this case the result will of course be better.

Did you try to run the query twice on the slower machine?
Did you check that EXPLAIN returns identical results on both machines?
Did you mysqladmin extended-status to find out exactly how many seeks
MySQL has to do ?  (This is described in the MySQL manual entry:


PS: The above doesn't say that you can't your query faster, it only
    means that it's hard for someone else to help you without access to
    your data.
Humbly looking for help in optimizing a queryRichard Dice11 Mar
  • Humbly looking for help in optimizing a queryMichael Widenius11 Mar
Re: new MySQL clientMichael Widenius20 Apr