On 29 Nov 2016, at 9:41pm, Kevin Baggett <kev...@ssec.wisc.edu> wrote:
> Here's the query:
> SELECT a.observation_day, a.observation_hour, a.observation_time,
> a.text_file_name, a.start_byte, a.message_length, a.wmo_header, a.wmo_prefix,
> max(a.rmkcorr_flag),b.wmo_ID,b.latitude,b.longitude from main.file_list a,
> main.station_list b WHERE a.wmo_ID=b.wmo_ID AND (a.wmo_prefix IN ("SA","SP"))
> GROUP BY a.wmo_ID, a.observation_time ORDER by a.observation_time;
>
> I put the following index on file_list:
> create index combo_index on
> file_list(wmo_prefix,wmo_ID,observation_time,rmkcorr_flag);
> on station_list:
> create index wmo_station_index on station_list (wmo_ID);
Add these …
CREATE INDEX combo_index2 ON
file_list(wmo_ID,latitude,wmo_prefix,observation_time);
CREATE INDEX wmo_station_index2 ON station_list (wmo_ID, longitude);
Run ANALYZE. See if anything improves.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
