I asked this a couple days ago, but never got an answer, so I'll try to include some more detail:
MySQL 4.0.12 on linux 2.4.18... I'm doing a join on 2 tables and trying to optimize it but I always end up with "Using temporary; Using filesort" and the query takes way too long... What can I do to speed this up? Here's my tables: CREATE TABLE `metardata` ( `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', `wmoid` int(10) default NULL, `temp` decimal(6,2) NOT NULL default '0.00', `rh` decimal(6,2) default NULL, `wdsp` decimal(6,2) default NULL, `wddir` char(2) default NULL, `dwpt` decimal(6,2) default NULL, `lpre` decimal(6,2) default NULL, `yearmoda` date NOT NULL default '0000-00-00', PRIMARY KEY (`stn`,`yearmoda`,`Hr`,`min`) ) TYPE=MyISAM CREATE TABLE `FcstHr_avn` ( `model` varchar(12) NOT NULL default '', `yearmoda` date NOT NULL default '0000-00-00', `modelruntime` int(2) NOT NULL default '0', `modelhr` 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, PRIMARY KEY (`yearmoda`,`modelruntime`,`modelhr`,`stn`), KEY `stn` (`stn`,`fyearmoda`,`fhr`), KEY `fcst` (`stn`,`yearmoda`,`modelruntime`) ) TYPE=MyISAM MAX_ROWS=4294967295 explain gives me this output: mysql> explain select modelruntime, modelhr, avg(m.temp-f.temp) from metardata as m, FcstHr_avn as f where m.yearmoda=f.fyearmoda and m.stn=f.stn and m.hr=f.fhr and m.yearmoda='2003-07-01' and m.stn='kdro' group by modelruntime+modelhr; +-------+------+---------------+---------+---------+-----------------------+ ------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+---------+---------+-----------------------+ ------+----------------------------------------------+ | m | ref | PRIMARY | PRIMARY | 7 | const,const | 18 | Using where; Using temporary; Using filesort | | f | ref | stn,fcst | stn | 11 | const,m.yearmoda,m.Hr | 27 | Using where | +-------+------+---------------+---------+---------+-----------------------+ ------+----------------------------------------------+ 2 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
