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
