On 27 Dec 2011, at 4:46pm, Pavel Ivanov wrote: >>> 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.
After an 'ANALYZE', perhaps. But without it my two forms give different 'EXPLAIN QUERY PLAN' results to one-another, though one of them is the same as the OP's version. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users