>>>>> "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.
Hi!
<cut>
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:
http://www.tcx.se/Manual_chapter/manual_Questions.html#Asking_questions
Regards,
Monty
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.
