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]
