[sqlite] Re: Index usage with LIKE queries

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:
I'm trying to get index used with LIKE queries:
CREATE TABLE test (name STRING);
CREATE INDEX test_name ON test (name);

LIKE is case-insensitive by default. To have it use your index, you need 
to either make the index case-insensitive:

CREATE INDEX test_name ON test (name COLLATE NOCASE);

or make LIKE case-sensitive:

PRAGMA case_sensitive_like = 1;

EXPLAIN QUERY PLAN SELECT * FROM test WHERE name LIKE 'aaa';

What's the point of using LIKE if you don't have any wildcards in the 
pattern?

Igor Tandetnik 


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------