Check the results of SHOW CREATE TABLE for the three tables you use and compare between production and development. You should be able to spot any differences in your key definitions.
If they are the same on both machines then you should probably run ANALYZE TABLE against the three tables on your production machine. That will update the query optimizer's statistics for those tables. If the optimizer has bad stats it can make poor choices about which index to use. If that doesn't help, try using the OPTIMIZE TABLE command on your three tables. Heavy fragmentation can slow down data retrieval, too. Let me know how things turn out. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Graham Cossey" <[EMAIL PROTECTED]> wrote on 11/11/2004 11:48:13 AM: > Thanks Shaun > > EXPLAIN shows the same 'possible keys' for each table but 'key' and > 'key-len' columns are different, as are the 'rows' as well of course. > > I guess this points to a probable difference in key definitions? > > Can 2 installations with the same table definitions produce different > results like this? Maybe something in the configs? > > Thanks > > Graham > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: 11 November 2004 16:28 > > To: Graham Cossey > > Cc: [EMAIL PROTECTED] > > Subject: Re: Help with query performance anomaly > > > > > > What does EXPLAIN show for the query on both systems? (I am wondering if > > you may have an index on your development system that you do not have on > > your production server.) > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > "Graham Cossey" <[EMAIL PROTECTED]> wrote on 11/11/2004 11:19:08 AM: > > > > > Hi > > > > > > Can someone offer any advice on a strange problem I have at present... > > > > > > If I run a certain query (see below) on my local development PC using > > > mysqlcc it returns in 3.7s. > > > > > > If I run the exact same query on my live webserver (again using > > mysqlcc) > > I > > > have yet to get a result !! > > > > > > Both databases have the same table definitions (live db originally > > created > > > from mysqldump of dev PC) and have exactly the same [number of] records > > in > > > each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora > > Core2, > > > live is RedHat 9. > > > > > > Other than this one query all else appears normal, any suggestions? > > > Let me know if you need more info and I'll attempt to supply it... > > > > > > Many thanks > > > > > > Graham > > > > > > Query: SELECT code, sum(qty) as total > > > FROM table1 as d, db2.table2 as r, table3 as p > > > WHERE year=2004 AND month=10 AND r.col1=p.col1 and d.code=p.code and > > > from_period <= 200410 and to_period > 200410 and d.col3!='6' > > > GROUP BY code > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > >
