> 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.
This specifics is the same for any DBMS: if you have greater/less condition on column that is "in the middle of index" then indexing on any consecutive columns is useless. I.e. in this case with index on (i_name, i_from, i_to) and condition i_from < something condition on i_to will be checked for each row satisfying conditions on i_name and i_from. So performance will be almost the same. "Almost" because when i_to is in the index optimization can be made and row from table not loaded unless condition on i_to is true. But I don't know if such optimization exists in SQLite or not. Also this optimization can be minimal in SQLite because AFAIK it stores part of the row from table in the index too. Pavel On Thu, May 13, 2010 at 10:16 AM, Jay A. Kreibich <j...@kreibi.ch> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
