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
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.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
