> 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 | +-------+------+------------------------------------------------------------ ----+---------+---------+-------------+-------+----------------------------- -----------------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
