Re: [sqlite] Optimizing a query with range comparison.

Hi, Everyone, I tried to use btree but I only noticed a slight increase of performance. I guess this module is better suited for more complex boundaries and does not really help one-dimensional searches that I am running. Because I am not sure if btree works for all python/sqlite installations, I

Re: [sqlite] Optimizing a query with range comparison.

On Tue, Dec 27, 2011 at 10:47 AM, Igor Tandetnik wrote: > If you need to do this with any regularity, you should look at R-Tree > module: > > http://www.sqlite.org/rtree.html I do have a lot of range-based queries and rtree seems to be a perfect solution for my problem. I am

Re: [sqlite] Optimizing a query with range comparison.

On 27 Dec 2011, at 5:36pm, Igor Tandetnik wrote: > On 12/27/2011 12:17 PM, Bo Peng wrote: >> sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a >> way to tell sqlite to return an id when it founds the first range that >> the id falls into? > > Without a temporary set to store

Re: [sqlite] Optimizing a query with range comparison.

On 12/27/2011 12:17 PM, Bo Peng wrote: sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a way to tell sqlite to return an id when it founds the first range that the id falls into? Without a temporary set to store the IDs it has already retrieved, how do you expect SQLite to

Re: [sqlite] Optimizing a query with range comparison.

>> 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 >>

Re: [sqlite] Optimizing a query with range comparison.

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

Re: [sqlite] Optimizing a query with range comparison.

On 12/27/2011 10:39 AM, 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);

Re: [sqlite] Optimizing a query with range comparison.

>> 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; >

Re: [sqlite] Optimizing a query with range comparison.

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

Re: [sqlite] Optimizing a query with range comparison.

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.

[sqlite] Optimizing a query with range comparison.

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