From: Michael Widenius Date: March 11 1999 1:44pm Subject: Humbly looking for help in optimizing a query List-Archive: http://lists.mysql.com/mysql/22 Message-Id: <14055.50998.474018.801005@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Richard" == Richard Dice 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! 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.