473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Join using temporary, filesort

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/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 4295

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. ...
0
by: Tom O'Neill | last post by:
Could anyone tell me the difference between the following two explains? It seems the first takes longer to execute. This first query is like so... select m.*, mi.age from members m,...
0
by: Jan Persson | last post by:
Hi, I am having trouble optimizing the query: select * from article order by article_id; for a MyISAM table that looks like this: CREATE TABLE `article` ( `id` int(11) NOT NULL...
0
by: Mark Smith | last post by:
Hello: I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ",
4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
0
by: Joe | last post by:
I'm trying to get the following query optimized: SELECT box.box_id, box.phone_number, buildings.name as building_name, buildings.id as building_id, accounts.name as account_name from...
7
by: stabbert | last post by:
I am attempting to join two tables together on two different unix servers. Here is some relevant info about the tables. TABLE 1 Setup ----------------------- DB2 UDB 7.2 EE
4
by: kamin | last post by:
I have this query select created_date, tyg_aging_due_dates.object_type, tyg_aging_due_dates.due_date from #tyg_aging_service_metrics right join tyg_aging_due_dates
4
by: polycom | last post by:
Assistance needed to optimize this query SELECT SD.content_id AS Id, SD.title AS Title, CT.name AS Contenttype, PV.content_id AS SponsorId, ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.