dc> If you are building an email indexing system, you problem
dc> want to use Full Text Search with fts3, not the LIKE
dc> operator. See
dc>http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
Thanks, interesting, but seems it's not my case, cause I'm doing search by
partial name/email
IT> Try
IT>
IT> select * from test where rowid in
IT> (select rowid from test where name like 'value'
IT> union all
IT> select rowid from test where email like 'value');
That seems to work...
explain query plan select * from test where rowid in (select rowid from test
where
name like "b%"
I get the index for LIKE working by specifying COLLATE in the CREATE TABLE:
CREATE TABLE test (name STRING COLLATE NOCASE), but...
IT> Besides, your statement uses OR in the WHERE clause. An index can't be
IT> used for such query even if you had plain old equality test in place of
IT> LIKE.
IT> LIKE is case-insensitive by default. To have it use your index, you need
IT> to either make the index case-insensitive:
IT>
IT> CREATE INDEX test_name ON test (name COLLATE NOCASE);
Sorry, tried to create the index this way, but it still isn't used by the query.
telega)
IT> LIKE is case-insensitive by default. To have it use your index, you need
IT> to either make the index case-insensitive:
Thanks! It's clear now.
IT> What's the point of using LIKE if you don't have any wildcards in the
IT> pattern?
Actually I do have wildcards, this was just a test, my
Hi
I'm trying to get index used with LIKE queries:
CREATE TABLE test (name STRING);
CREATE INDEX test_name ON test (name);
EXPLAIN QUERY PLAN SELECT * FROM test WHERE name = 'aaa';
The output is:
0|0|TABLE test WITH INDEX test_name
i.e. when LIKE is not used, the index is involved, everything is
6 matches
Mail list logo
