On 27 Dec 2011, at 3:39pm, Bo Peng wrote: > 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);
First, thanks a lot for posting that, which saves us all a huge amount of guessing. > 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 So you don't care how many ranges in tableB an object falls into, you just want it to appear once ? Or your data is structures so that ranges don't overlap ? Either way, what you really want for ids is something more like CREATE TABLE ids (id INTEGER PRIMARY KEY, ON CONFLICT IGNORE); To understand this better, read <http://www.sqlite.org/lang_createtable.html#rowid> > INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end; Try using a JOIN instead. In fact, try both ways around: DELETE FROM ids; INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN start AND end; then try DELETE FROM ids; INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start AND end; Which one is faster depends on some aspects about your data and it's easier for you to test it than for me to guess. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
