On Sat, 16 Oct 2004 04:51:36 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > Mike Ponomarenko wrote: > > In sqlite3 queries using LIKE and BETWEEN do not use existing indices. So for > > a schema like > > CREATE TABLE t (a integer, b char(40)); > > CREATE INDEX t_idx_0 ON t(a); > > CREATE INDEX t_idx_1 ON t(b); > > queries like > > "SELECT * FROM t WHERE a BETWEEN 1 AND 20" or > > "SELECT * FROM t WHERE b LIKE 'abc%'" > > end up doing a table scan. > > > > Just curious if there are any changes in the works to make queries such as above > > use available indices? > > No
Okie dokie. > > > For BETWEEN: Integer 2, MoveGE, Next until > 20, etc. > > For LIKE: if one parameter is a string with a constant prefix, String8 'abc', > > MoveGE, Next until Function(LIKE) fails, etc. > > > > I realize that queries can be rewritten to make them more efficient (SELECT * > > FROM t WHERE a >= 1 AND a <= 20), etc., but it's not always feasible. I'd > > appreciate any thoughts on the matter. Thanks! > > > > Every effort is made to keep SQLite simple, small, and fast. Adding > lots of code to do expression optimizations that could just as easily > have been done by the user is contrary to those goals. My whole point here is that to a guy writing SQL it's not obvious that WHERE a >=1 .... is different from BETWEEN ... and that one is far more efficient than the other. For larger databases scanning tables is not an option. If users are to be told not to use these features, they may as well not be there. I don't think the major bottleneck in the engine will be extra cases in the parser -- SQLite will remain small and fast, and not much more complicated. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > >
