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
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 using the sqlite modu
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
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
>> 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
>> star
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 BETWEE
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);
t
>> 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;
> IN
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 T
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.
Matchi
> 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
>dpi.
> 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 ge
> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
>
> You can step straight through the index in linear time. But
> for each index entry you encounter, you have to look up a
> record in the main table in order to get the data. It's the
> second step, the table look
> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 14, 2004 10:13 AM
> To: Michael Hunley; [EMAIL PROTECTED]
> Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Optimizing a query
>
>
> But it is also still really
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
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 it
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 JOIN
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 linea
> -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
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 B
> -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
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 an
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).
>
>
23 matches
Mail list logo