Re: [sqlite] LIKE, BETWEEN

[Raymond Irving]

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!
> 

[sqlite] LIKE, BETWEEN

[Mike Ponomarenko]

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!