What are the configuration you are using? What's the size of your buffers? What's your system?
Maybe increasing sort buffer and key buffer will be good. ;) Alexis Quoting Brad Teale <[EMAIL PROTECTED]>: > Hello, > > The problem: > I have the following query with is taking upwards of 2 minutes to complete > and we need it faster, prefer less than 30 seconds (don't laugh): > select modelhr, avg(f.temp-b.temp), avg(abs(f.temp-b.temp)), > stddev(f.temp-b.temp), stddev(abs(f.temp-b.temp)), count(f.temp-b.temp) from > foo as f, bar as b where f.fyearmoda=b.yearmoda and f.fhr=b.hr and > f.stn=b.stn and b.yearmoda >= '2003-01-01' and b.yearmoda <= '2003-01-31' > and b.stn='QQQQ' group by modelhr; > > When we run explain we get: > +-------+-------+-------------------+---------+---------+------------------- > ----+----------------------------------------------------+ > | table | type | possible_keys | key | key_len | ref > | rows | Extra | > +-------+-------+-------------------+---------+---------+------------------- > ----+----------------------------------------------------+ > | b | range | PRIMARY,interp_hr | PRIMARY | 7 | NULL > | 679 | Using where; Using temporary; Using filesort | > | f | ref | stn,fcst | stn | 11 | > const,m.yearmoda,m.Hr | 26 | Using where > | > +-------+-------+-------------------+---------+---------+------------------- > ----+------+----------------------------------------------+ > > Is there a reasonable way to get this query from using temporary and > filesort? I tried dumping the data into a temporary table, and the explain > ran the same. Also, both MySQL setups perform the same. Any > ideas....Please! > > -------------------------------- System/Table Stuff Below > ------------------------- > System: dual Xeon 2.4GHz machine with 2G RAM > Interconnect: QLogicFC 2200 > Disk1: Sun T3 hardware raid5 with Reiserfs (64M cache controller) > Disk2: Sun T3 hardware raid0 with Reiserfs (64M cache controller) > OS: Red Hat Linux release 8.0 (with qlogicfc module) > MySQL1: 4.0.14 - prebuilt MySQL rpm uses Disk1 > MySQL2: 4.0.15a - Hand built with MySQL Manual options uses Disk2 > > The table structures are as follows: > CREATE TABLE foo ( > yearmoda date NOT NULL default '0000-00-00', > mruntime int(2) NOT NULL default '0', > mhr int(3) NOT NULL default '0', > fyearmoda date NOT NULL default '0000-00-00', > fhr int(2) NOT NULL default '0', > stn varchar(4) NOT NULL default '', > temp decimal(6,2) default NULL, > ... more but unused data here > PRIMARY KEY (yearmoda,mruntime,mhr,stn), > KEY stn (stn,fyearmoda,fhr), > KEY fcst (stn,yearmoda,mruntime) > ) TYPE=MyISAM; > > CREATE TABLE bar ( > stn char(4) NOT NULL default '', > hr int(2) NOT NULL default '0', > min int(2) NOT NULL default '0', > day int(2) NOT NULL default '0', > temp decimal(6,2) NOT NULL default '0.00', > ... More unused data here > yearmoda date NOT NULL default '0000-00-00', > PRIMARY KEY (stn,yearmoda,hr,min), > KEY interp_hr (yearmoda,hr,stn) > ) TYPE=MyISAM; > > Table Stats: > foo - 38G - Data/18G - Index (326K rows) > bar - 24G - Data/14G - Index (35K rows) > > > Thanks, > Brad > > -- > 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]
