>> 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.
If these two don't behave identically to each other and don't behave identically to the original query then there's bug in SQLite. With inner join it shouldn't matter for optimizer which form your query is written in. For OP: please issue "EXPLAIN QUERY PLAN" instead of "EXPLAIN" on your query. It will give more understandable information on how SQLite processes your query. Pavel On Tue, Dec 27, 2011 at 11:39 AM, Simon Slavin <[email protected]> wrote: > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
