Hi,
I am trying to speed up a query on my SQLite database using SQLite
version 3.7.17 2013-05-20 00:56:22.
The daily database has 2 tables: file_list and station_list.
Yesterday's database had a file_list table of over 1.7 million records.
station_list is pretty much constant at 21549 records.
For my query, I am looking for records that have a wmo_prefix field of
"SA" and "SP".
So, that reduces the records from file_list to 363,710.
I have a field "rmkcorr_flag" that has a value of 0 to 3 based on the
underlying data that the record is referring to.
What I want returned is the record with the maximum of the rmkcorr_flag
for the associated wmo_ID and observation_time (e.g. 2 for KMSN at 213347Z)
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);
I ran ANALYZE and EXPLAIN QUERY PLAN.
ANALYZE: table sqlite_stat1 shows file_list|combo_index|1708131 18172 43 5 4
station_list|wmo_station_index|21549 2
EXPLAIN QUERY PLAN:
0|0|0|SEARCH TABLE file_list AS a USING INDEX combo_index (wmo_prefix=?)
(~36344 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|1|SEARCH TABLE station_list AS b USING INDEX wmo_station_index
(wmo_ID=?) (~2 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY
So, when I first run the query in the database with .timer on, it hangs
and hangs and hangs, and then says:
CPU Time: user 3.920404 sys 1.800726
I didn't measure the hanging time, but it was definitely over a minute,
if not two. ( I see later versions of SQLite have an elapsed time)
Subsequent runs of the query match up with the times above.
Seems like there is some kind-of loading into cache the first time???
Anyway, I'm not a heavy SQLite/database user, so please be gentle :-)
I just want to see if there is anything that stands out to anyone that I
can do to speed up my query, especially the first time through.
I can/should update the SQLite version, but I don't think that's the
problem.
Thanks for any help!
Kevin
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
