I do something similar, where the ranges are zip codes. However, my tableb is arranged vertically with one key (zip code) and one value (geographic zone). I would then join the two tables using the zip code, rather than trying to identify the zip code within a range of zip codes in tableb. Matching 3 million tablea rows to 49,000 tableb rows takes 90 seconds, I think.
create tablea (id int, pos int); create tableb (pos int, ?? int); /* not sure what is represented by index position of range */ create table ids as select ?? from tableb b join tablea a on b.pos = a.pos; On Tue, Dec 27, 2011 at 9:39 AM, Bo Peng <ben....@gmail.com> wrote: > Dear Sqlite experts, > > The schema of my test tables is as follows: > > CREATE TABLE tableA (id INT, pos INT); > CREATE TABLE tableB (start INT, end INT); > > CREATE INDEX tableA_idx on tableA (pos asc); > CREATE INDEX tableB_idx on tableB (start asc, end asc); > > CREATE TABLE ids (id INT); > > tableA saves position of about 8 million objects, and table B saves > about 40 thousand ranges. I need to find out all ids in tableA that > falls into one of the ranges in tableB, and insert the results into > table ids. I am using a query > > INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND > end; > > with indexes on tableA.pos and tableB.start, tableB.end (combined), > this query takes hours to execute. Is there anyway to optimize this > query? My understanding is that, if the query takes each pos and > compare it to all ranges, it will be slow. If it takes each range and > get all pos fall into the range, the query will be much faster. I have > tried to 'EXPLAIN' the query but I do not understand the output > because it looks different from what is described in > http://www.sqlite.org/eqp.html. I will appreciate it if someone can > tell me what sqlite is doing for this query. > > > explain select id from tableA, tableB where pos between start and end; > 0|Trace|0|0|0||00| > 1|Goto|0|26|0||00| > 2|OpenRead|1|2|0|2|00| > 3|OpenRead|0|1446|0|2|00| > 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00| > 5|Rewind|1|22|0||00| > 6|Column|1|0|1||00| > 7|IsNull|1|21|0||00| > 8|Affinity|1|1|0|d|00| > 9|SeekGe|2|21|1|1|00| > 10|Column|1|1|1||00| > 11|IsNull|1|21|0||00| > 12|Affinity|1|1|0|d|00| > 13|IdxGE|2|21|1|1|01| > 14|Column|2|0|2||00| > 15|IsNull|2|20|0||00| > 16|IdxRowid|2|2|0||00| > 17|Seek|0|2|0||00| > 18|Column|0|0|3||00| > 19|ResultRow|3|1|0||00| > 20|Next|2|13|0||00| > 21|Next|1|6|0||01| > 22|Close|1|0|0||00| > 23|Close|0|0|0||00| > 24|Close|2|0|0||00| > 25|Halt|0|0|0||00| > 26|Transaction|0|0|0||00| > 27|VerifyCookie|0|6|0||00| > 28|TableLock|0|2|0|tableB|00| > 29|TableLock|0|1446|0|tableA|00| > 30|Goto|0|2|0||00| > > Many thanks in advance, > Bo > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
