This is very strange. Common sense will tell us that a BETWEEN call on the "a" column should use the index "t_idx_0". I can't see why SQLite is doing a table scan.
Is this another one of those code-optimized features of SQLite to forget intelligent parsing and processing in order to reduce DLL size? __ Raymond Irving --- Mike Ponomarenko <[EMAIL PROTECTED]> 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? > 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! >
