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!
