Response at end.... "Graham Cossey" <[EMAIL PROTECTED]> wrote on 11/11/2004 12:19:17 PM:
> > 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? > > > [snip] > > > > > > > > > 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.) > > > > [snip] > > > > > > > > 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 > > > > > [snip] > > I've done mysqldumps of the tables involved on both machines and the create > table definitions and key definitions are identical. > > The results of my EXPLAINs are pasted below. > > Thanks > Graham > > DEV BOX: > > EXPLAIN SELECT d.dcode, sum(qty) as total > FROM table1 as d, db2.table2 as r, table3 as p > WHERE year=2004 AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and > from_period <= 200410 and to_period > 200410 and d.region!='6' > GROUP BY dcode > > +-------+-------+----------------------------------------------------------- > -----+----------+---------+---------------------+------+-------------------- > --------------------------+ > | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > +-------+-------+----------------------------------------------------------- > -----+----------+---------+---------------------+------+-------------------- > --------------------------+ > | d | ALL | [NULL] > | [NULL] | [NULL] | [NULL] | 322 | Using where; Using > temporary; Using filesort | > | p | index | PRIMARY > | PRIMARY | 19 | [NULL] | 6082 | Using where; Using index > | > | r | ref | > PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st | yr_mn_pc | > 13 | const,const,p.pcode | 41 | Using where > | > +-------+-------+----------------------------------------------------------- > -----+----------+---------+---------------------+------+-------------------- > --------------------------+ > > LIVE SERVER: > > EXPLAIN SELECT d.dcode, sum(qty) as total > FROM table1 as d, db2.table2 as r, table3 as p > WHERE year=2004 AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and > from_period <= 200410 and to_period > 200410 and d.region!='6' > GROUP BY dcode > > +-------+------+------------------------------------------------------------ > ----+---------+---------+-------------+-------+----------------------------- > -----------------+ > | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > +-------+------+------------------------------------------------------------ > ----+---------+---------+-------------+-------+----------------------------- > -----------------+ > | d | ALL | [NULL] > | [NULL] | [NULL] | [NULL] | 322 | Using where; Using temporary; > Using filesort | > | r | ref | > PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st | PRIMARY | > 8 | const,const | 89618 | Using where | > | p | ref | PRIMARY > | PRIMARY | 4 | r.pcode | 2 | Using where; Using index > | > +-------+------+------------------------------------------------------------ > ----+---------+---------+-------------+-------+----------------------------- > -----------------+ > > These are two different plans. Your development machine is using the index yr_mn_pc on the r table and is joining that table last. On your production server, the r table is joined second and is joined by the index PRIMARY. Let me know how the ANALYZE TABLE I suggested in a previous message works out to help the statistics. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
