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.

Re: [sqlite] optimizing a query with ORDER BY and LIMIT

> CREATE INDEX dpi5 on device_perf_interval( > interval_end_date, > interval_type, > interval_duration > ); > > explain query plan > SELECT d.device_type, dpi.* > FROM device d, device_perf_interval dpi > WHERE d.device_id=dpi.device_id AND >dpi.interval_type=1 AND >

Re: [sqlite] optimizing a query with ORDER BY and LIMIT

> SELECT d.device_type, dpi.* FROM device d, device_perf_interval dpi > WHERE d.device_id=dpi.device_id AND dpi.interval_type=1 AND > dpi.interval_duration=300 > ORDER BY dpi.interval_end_date LIMIT ; > > What can I do to speed this up? I tried a third index on interval_end_date > but can't

Re: [sqlite] Re: [inbox] Re: [sqlite] Optimizing a query

At 11:12 AM 1/14/2004 -0500, D. Richard Hipp wrote: SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz'; In the original query, the result was indeed a count(*) so no access to the data we required there. But access to the data was required in order to evaluate the WHERE clause. So it

Re: [sqlite] Re: [inbox] Re: [sqlite] Optimizing a query

Michael Hunley wrote: At 10:37 AM 1/14/2004 -0500, D. Richard Hipp wrote: In some cases you can avoid the O(logN) lookup of the main table entry and just use the index. For example: SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz'; Wasn't that the original question, Ken? Except

[sqlite] Re: [inbox] Re: [sqlite] Optimizing a query

At 10:37 AM 1/14/2004 -0500, D. Richard Hipp wrote: In some cases you can avoid the O(logN) lookup of the main table entry and just use the index. For example: SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz'; Wasn't that the original question, Ken? Except it was a count(*) on a

Re: [sqlite] Optimizing a query

Williams, Ken wrote: So, no way to make it O(N)? If the two indexes could be Retrieving a single record from a BTree is an O(logN) operation. Doing so N times gives O(NlogN). Oh, I thought it was also possible to step straight through an index, You can step straight through the index in

RE: [sqlite] Optimizing a query

> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 14, 2004 9:22 AM > To: Williams, Ken > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Optimizing a query > > > Williams, Ken wrote: > > > > So, no w

Re: [sqlite] Optimizing a query

Williams, Ken wrote: CREATE INDEX whatever ON output(verb_id,tag); That will make it O(NlogN) instead of O(N**2). So, no way to make it O(N)? If the two indexes could be iterated together, as in the following pseudocode, it would seem to be an O(N) operation. Retrieving a single record from a

RE: [sqlite] Optimizing a query

> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 13, 2004 6:17 PM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Optimizing a query > > > > Can anyone suggest a good way to optimize the following query? &g

[sqlite] Re: [inbox] Re: [sqlite] Optimizing a query

At 07:17 PM 1/13/2004 -0500, D. Richard Hipp wrote: Actually, SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make

Re: [sqlite] Optimizing a query

Can anyone suggest a good way to optimize the following query? SELECT count(*) FROM propositions p, output o WHERE p.verb_id=o.verb_id AND p.tag=o.tag AND (p.stop!=o.stop OR p.start!=o.start); CREATE INDEX whatever ON output(verb_id,tag); That will make it O(NlogN) instead of O(N**2). >