On Wed, May 12, 2010 at 06:00:29PM +0200, Jan Asselman scratched on the wall: > 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);
The query is only going to be able to use one of these. > 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... True, although R-trees become much more useful when you get past one dimension. > All suggestions or corrections are appreciated. Assuming i_name is somewhat unique, why wouldn't you just create an index over (i_name, i_from, i_to)? If i_name isn't very unique, mix up the order a bit. You have three basic conditions, and they're all AND'ed together. Just build an index that each condition can walk through. Or am I missing something? I know there are some odd rules about how SQLite will use (or won't use) indexes for greater-than/less-than conditions, but I don't remember the specifics. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
