On Thu, May 13, 2010 at 10:49:48AM -0400, Pavel Ivanov scratched on the wall: > > ?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.
Not nearly as useless as it would be at the beginning of the index. > 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. Right... you still have to do an index scan, but you can do that all from the index, rather than the double-lookup required to reference back to the table. Normally GT/LT type constraints are not applied to an index because it is assumed the index won't be targeted enough to justify the overhead. Hence structures like R*Trees that can deal with multiple constraints much more efficiently. But in this case every step is a narrowing step (especially if i_name is reasonably unique) and the whole set of constraints can be processed from a single index. > But I don't know if such optimization exists in SQLite or not. Yeah, I don't know. I also don't know if SQLite *always* ignores the indexes. If you have extended stats enabled, the ANALYZE command will generate a histogram. It seems the only use of the histogram is to make a choice on using (or not using) an index for GT/LT type lookups. -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
