I would first create an INTEGER primary key and then place an index on name,
another on i_from, and another on i_to, and then see if the approach below
has any benefit.

When I tried this with a geo-queryit was actually slower than the standard
select, and I'm curious if that's always going to be the case. It will come
down to how efficient the INTERSECT of the vectors of integers is. Each
vector will have been the result of an index-scan.  If INTERSECT were
optimized (perhaps with a minimal perfect hash function
http://cmph.sourceforge.net/index.html) this approach might be useful.


select * from T
JOIN
(
select pk_col from T where i_from > ?
intersect
select pk_col from T where i_to < ?
) as DESIREDINTERVAL
ON T.pk_col = DESIREDINTERVAL.pk_col
and T.name = ?


Regards
Tim Romano
Swarthmore PA

On Wed, May 12, 2010 at 12:00 PM, Jan Asselman <jan.assel...@iba-benelux.com
> wrote:

> Hi,
>
> Given the following table with large row count 'row_count':
>
> CREATE TABLE table
> (
>        i_name  TEXT,
>        i_from  INTEGER,
>        i_to            INTEGER,
>        i_data  BLOB
> )
>
> I am wondering what would be the fastest way to get all rows with a
> given name 'myname' that intersect with a given interval [a, b]?
>
>
> CREATE INDEX idx_from ON table (i_name, i_from);
> CREATE INDEX idx_to ON table (i_name, i_to);
>
> SELECT data FROM table WHERE name = 'myname' AND i_from < b AND i_to > a
>
>        -> index idx_from will be used
>        -> in worst case (a is larger than all i_to) all 'myname' rows
>           will be traversed before concluding result set is empty
>
> SELECT data FROM table WHERE name = 'myname' AND i_to > a AND i_from < b
>
>        -> index idx_to will be used
>        -> in worst case (b is smaller than all i_from) all 'myname'
> rows
>           will be traversed before concluding result set is empty
>
>
>
> I know this is exactly what a one dimensional R-tree index is used for,
> but my project requires 64 bit integer minimum- and maximum-value
> pairs...
>
> All suggestions or corrections are appreciated.
>
>
> Thanks in advance,
>
> Jan Asselman
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to