RE: Help with query performance anomaly
Thanks for the advice Steven, I'll bear it in mind and do some reading. Graham > -Original Message- > From: Steven Roussey [mailto:[EMAIL PROTECTED] > Sent: 13 November 2004 02:52 > To: 'Graham Cossey' > Cc: [EMAIL PROTECTED] > Subject: RE: Help with query performance anomaly > > > For production systems, I would never let the mysql optimizer > guess a query > plan when there are joins of big tables and you know exactly how it should > behave. Once you think a query is finished, you should optimize > it yourself. > Use STRAIGHT_JOIN and USE INDEX as found here in the manual: > > http://dev.mysql.com/doc/mysql/en/JOIN.html > > STRAIGHT_JOIN is identical to JOIN, except that the left table is always > read before the right table. This can be used for those (few) cases for > which the join optimizer puts the tables in the wrong order. > > http://dev.mysql.com/doc/mysql/en/SELECT.html > > The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the > optimizer hints > about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. > > -steve-- > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
For production systems, I would never let the mysql optimizer guess a query plan when there are joins of big tables and you know exactly how it should behave. Once you think a query is finished, you should optimize it yourself. Use STRAIGHT_JOIN and USE INDEX as found here in the manual: http://dev.mysql.com/doc/mysql/en/JOIN.html STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order. http://dev.mysql.com/doc/mysql/en/SELECT.html The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly (SOLVED)
It turns out that it appears to be a data discrepancy that caused the query optimiser to, well, not optimise. I thought the main table (r) with 3million records would be the problem, but it was table p with 3100 records on the live server and 3082 records on my dev pc that caused the problem. Although the results of show create table etc were identical on both machines, uploading the data from dev to live has solved the problem. Thanks to all that offered advice. Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
[snip] > > Have just run 'top' on the live server... > > Before running the query I get: > > 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44 > 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped > CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% > idle > CPU1 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% > idle > CPU2 states: 0.0% user 0.1% system0.0% nice 0.0% iowait 99.4% > idle > CPU3 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% > idle > Mem: 6203744k av, 6194148k used,9596k free, 0k shrd, 304848k > buff > 1948476k active,3601304k inactive > Swap: 4192956k av, 1876604k used, 2316352k free 4081216k > cached > > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND > 7622 15 0 904 904 748 R 0.1 0.0 0:00 2 top > 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init > 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 1 syslogd > 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 0 xinetd > 3393 root 8 0 576 552 512 S 0.0 0.0 0:21 1 crond > 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 2 sshd > 3264 root 8 0 3676 1548 1488 S 0.0 0.0 0:37 2 httpd > 15296 apache 9 0 9904 8872 4752 S 0.0 0.1 0:00 2 httpd > 4576 apache 9 0 9876 8804 4344 S 0.0 0.1 0:01 1 httpd > 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 > mysqld_safe > 10433 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 11360 mysql 8 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 11395 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld > 11425 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 11456 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 3 mysqld > 11491 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 12128 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 12162 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld > 12193 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 12224 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 1 mysqld > 32418 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld > 5284 9 0 2288 2244 2068 S 0.0 0.0 0:00 3 sshd > 5538 9 0 1292 1292 984 S 0.0 0.0 0:00 1 bash > > Now, I don't know if it's coincidental or not but after setting the query > running and re-issuing the top command I get: > > 13:59:49 up 45 days, 11:51, 1 user, load average: 0.98, 0.61, 0.53 > 26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped > CPU0 states: 75.0% user 25.0% system0.0% nice 0.0% iowait 0.0% > idle > CPU1 states: 76.0% user 24.0% system0.0% nice 0.0% iowait 0.0% > idle > CPU2 states: 63.0% user 36.0% system0.0% nice 0.0% iowait 0.0% > idle > Floating point exception > > Does not look good to me !! > [snip] I have now managed to get a top while the query is running: 14:29:52 up 45 days, 12:21, 1 user, load average: 0.69, 0.28, 0.39 25 processes: 23 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 71.1% user 28.0% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 68.0% user 31.0% system0.0% nice 0.0% iowait 0.0% idle CPU2 states: 71.0% user 28.0% system0.0% nice 0.0% iowait 0.0% idle CPU3 states: 80.0% user 19.0% system0.0% nice 0.0% iowait 0.0% idle Mem: 6203744k av, 5764148k used, 439596k free, 0k shrd, 257900k buff 1839520k active,3282316k inactive Swap: 4192956k av, 1881496k used, 2311460k free 3687672k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 19462 mysql 14 0 19968 19M 2016 R95.4 0.3 0:38 0 mysqld 25248 10 0 1004 1004 748 R 0.3 0.0 0:00 3 top 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 3 syslogd 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 3 xinetd 3393 root 9 0 576 552 512 S 0.0 0.0 0:21 2 crond 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 0 sshd 3264 root 9 0 3676 1548 1500 S 0.0 0.0 0:37 1 httpd 15296 apache 9 0 10632 9608 4768 S 0.0 0.1 0:01 0 httpd 4576 apache 9 0 10036 8964 4344 S 0.0 0.1 0:01 3 httpd 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 mysqld_safe 10433 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 1 mysqld 11360 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 2 mysqld 11395 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 0 mysqld 11425 mysql 9 0 19968
RE: Help with query performance anomaly
Everything seems fine except for this one query. I'm not sure quite what the results of top will say or what they mean if it's a Virtual Private Server environment? Nothing untoward in /var/log/messages or var/log/httpd/error_log or virtual host httpd logs. Have just run 'top' on the live server... Before running the query I get: 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU1 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU2 states: 0.0% user 0.1% system0.0% nice 0.0% iowait 99.4% idle CPU3 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle Mem: 6203744k av, 6194148k used,9596k free, 0k shrd, 304848k buff 1948476k active,3601304k inactive Swap: 4192956k av, 1876604k used, 2316352k free 4081216k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 7622 15 0 904 904 748 R 0.1 0.0 0:00 2 top 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 1 syslogd 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 0 xinetd 3393 root 8 0 576 552 512 S 0.0 0.0 0:21 1 crond 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 2 sshd 3264 root 8 0 3676 1548 1488 S 0.0 0.0 0:37 2 httpd 15296 apache 9 0 9904 8872 4752 S 0.0 0.1 0:00 2 httpd 4576 apache 9 0 9876 8804 4344 S 0.0 0.1 0:01 1 httpd 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 mysqld_safe 10433 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11360 mysql 8 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11395 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 11425 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11456 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 3 mysqld 11491 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12128 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12162 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 12193 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12224 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 1 mysqld 32418 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 5284 9 0 2288 2244 2068 S 0.0 0.0 0:00 3 sshd 5538 9 0 1292 1292 984 S 0.0 0.0 0:00 1 bash Now, I don't know if it's coincidental or not but after setting the query running and re-issuing the top command I get: 13:59:49 up 45 days, 11:51, 1 user, load average: 0.98, 0.61, 0.53 26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 75.0% user 25.0% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 76.0% user 24.0% system0.0% nice 0.0% iowait 0.0% idle CPU2 states: 63.0% user 36.0% system0.0% nice 0.0% iowait 0.0% idle Floating point exception Does not look good to me !! Comments? Advice? Thanks Graham > -Original Message- > From: Jamie Kinney [mailto:[EMAIL PROTECTED] > Sent: 11 November 2004 19:25 > To: Graham Cossey > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Help with query performance anomaly > > > How do the OS statistics look on both boxes. Do top, sar, vmstat or > iostat show any CPU, memory or I/O performance issues? Does anything > odd appear in the /var/log/messages file? > > -Jamie > > On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey > <[EMAIL PROTECTED]> wrote: > > > > > > [big snip] > > > > > > > > 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. > > > > > > > I have run ANALYZE on all tables on the live server and the result of > > EXPLAIN is the same as before. > > > > Any further suggestions? I'm off to double check the create > table stuff once > > more... > > > > Thanks for your help (and patience!!) > > > > Graham > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query performance anomaly
How do the OS statistics look on both boxes. Do top, sar, vmstat or iostat show any CPU, memory or I/O performance issues? Does anything odd appear in the /var/log/messages file? -Jamie On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey <[EMAIL PROTECTED]> wrote: > > > [big snip] > > > > > 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. > > > > I have run ANALYZE on all tables on the live server and the result of > EXPLAIN is the same as before. > > Any further suggestions? I'm off to double check the create table stuff once > more... > > Thanks for your help (and patience!!) > > Graham > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
[big snip] > > 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. > I have run ANALYZE on all tables on the live server and the result of EXPLAIN is the same as before. Any further suggestions? I'm off to double check the create table stuff once more... Thanks for your help (and patience!!) Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
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
RE: Help with query performance anomaly
> 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]
RE: Help with query performance anomaly
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] > > > > > > >
RE: Help with query performance anomaly
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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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] >
