Join using temporary, filesort

[Partap Davis]

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`,`yearmod a`,`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`,`mo delruntime`,`mo delhr`,`stn`),
KEY `stn` (`stn`,`fyearmo da`,`fhr`),
KEY `fcst` (`stn`,`yearmod a`,`modelruntim e`)
) TYPE=MyISAM MAX_ROWS=429496 7295
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.fy earmoda and m.stn=f.stn
and m.hr=f.fhr and m.yearmoda='200 3-07-01' and m.stn='kdro' group by
modelruntime+mo delhr;
+-------+------+---------------+---------+---------+-----------------------+
------+----------------------------------------------+
| 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.yearmod a,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/my***********...ie.nctu.edu.tw