Mark Lawrence wrote: > The documentation for partial indexes (section 3.2) lists "=, <, >, <=, > >=, <>, or IN" as operators that will trigger the use of an > index WHERE c IS NOT NULL. > > It seems to me that LIKE should also be in that list, given that it > will also only match when c is not null.
A custom LIKE function could behave differently. > I can force the query planner to say it will use the index by querying > as such: > > WHERE col IS NOT NULL and col LIKE 'expr%' > > But am I correct in thinking that SQLite is still scanning though the > table to evaluate expr%? SQLite will scan the index and then access the table only for matching rows, because this is faster because the index has fewer columns than the table and thus requires less I/O. If you're doing "SELECT col", SQLite does not need to access the table at all. > If so, then am I also correct in thinking that if I want LIKE to use > an index it has to be a full index? The index is used for scanning. If you want to avoid scans, you must use operators that cannot be customized, i.e., >, <, or BETWEEN. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
