mysql - Subqueries and Large Tables. How do I Improve The Speed? -

i'm not new mysql, i'm way in on head here.

i'd show table of differences in temperatures panama , belize based on date , atmospheric level. query supposed match panama , belize data based on date , atmospheric level , return top 30 differences, ordered extent of differences.

however, incredibly slow (over 30s) times out. other queries i've written dataset slow (about 26s). if run subqueries, take 1.7s or so. should note both of tables below on 440,000 rows long, though don't think that's large. problem way i'm joining tables or way i'm creating subqueries.

here's setup: (it's sql the exported tables. i'm omitting columns)

/**the table panama weather data */   create table `panama_weather_data` (   `id` varchar(40) not null,   `owmpackageid` varchar(30) not null,   `level` float default null,   `dt` date default null,   `temperature` float default null, ) engine=innodb default charset=latin1;  alter table `panama_weather_data`   add primary key (`id`) using btree; commit;  /**the table belize weather data*/  create table `belize_weather_data` (   `id` varchar(40) not null,   `owmpackageid` varchar(30) not null,   `level` float default null,   `dt` date default null,   `temperature` float default null,  ) engine=innodb default charset=latin1;  alter table `belize_weather_data`   add primary key (`id`) using btree; commit;  /**code populate tables here*/ 

and here's query:

select abs(panamatemperature-belizetemperature) temperaturedif,  panamaatmostphericlevel, panamatable.dt                   (select cast(panama_weather_data.dt datetime) dt,            panama_weather_data.level panamaatmostphericlevel,           panama_weather_data.temperature panamatemperature          panama_weather_data          panama_weather_data.owmpackageid = 'openweathermappkg19758'  )           panamatable          join           (select cast(belize_weather_data.dt datetime) dt,           belize_weather_data.level belizeatmosphericlevel,           belize_weather_data.temperature belizetemperature          belize_weather_data          belize_weather_data.owmpackageid = 'openweathermappkg19758' )           belizetable          on panamaatmostphericlevel = belizeatmosphericlevel          , panamatable.dt = belizetable.dt          order temperaturedif          limit 30 

my question really: there anyway optimize query , make less painful?

cast(panama_weather_data.dt datetime) dt

why? (all slow down query)

is there anyway optimize query

the sql select statement have shown not starting point. did not tell how intend query data in future. specifically, going examine of data each time run query?

your biggest win comes not keeping data in separate tables - should single table different attributes 2 datasets.

after that, next biggest improvement come storing temperature difference in table , indexing it.