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
