[sqlite] select intersecting intervals

[Jan Asselman]

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