I upgraded to 3.7.1.1 and tested this...glob works wonderfully...especially when there are few hits it makes a huge difference since it uses the index.
I don't see why like shouldn't be using the index in these cases as that's what the docs appear to say it should do. A%A meets the qualifications for it. I had a dictionary of 1M word phrases I used. sqlite> pragma cache_size=200000; sqlite> .separator ',' sqlite> create table words(id integer,word text); sqlite> .import 1000000.dat words sqlite> create index idx on words(word); sqlite> .timer on sqlite> select count(*) from words where word glob 'P*P'; 4241 CPU Time: user 0.250000 sys 0.000000 sqlite> select count(*) from words where word like 'P%P'; 4241 CPU Time: user 0.328125 sys 0.000000 sqlite> select count(*) from words where word like 'A%A'; 0 CPU Time: user 0.281250 sys 0.000000 sqlite> select count(*) from words where word like 'Z%Z'; 0 CPU Time: user 0.281250 sys 0.000000 sqlite> select count(*) from words where word glob 'Z*Z'; 0 CPU Time: user 0.000000 sys 0.000000 sqlite> select count(*) from words where word glob 'L*P'; 136 CPU Time: user 0.015625 sys 0.000000 sqlite> select count(*) from words where word like 'L%P'; 136 CPU Time: user 0.281250 sys 0.000000 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Sunday, July 10, 2011 12:03 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] using index when using concatination 3.7.4 doesn't indicate it will use an index in either case with like...but glob seems to call the index...why does glob use the index but like does not? sqlite> create table words(word text); sqlite> create index idx on words(word); sqlite> explain query plan select * from words where "word" like 'a' || '%' || 'a'; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE words (~500000 rows) sqlite> explain query plan select * from words where word like 'a%a'; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE words (~500000 rows) sqlite> explain query plan select * from words where word like 'a%'; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE words (~500000 rows) sqlite> explain query plan select * from words where word glob 'a%'; sele order from deta ---- ------------- ---- ---- 0 0 0 SEARCH TABLE words USING COVERING INDEX idx (word>? AND word<?) (~55000 rows) sqlite> explain query plan select * from words where word glob 'a%b'; sele order from deta ---- ------------- ---- ---- 0 0 0 SEARCH TABLE words USING COVERING INDEX idx (word>? AND word<?) (~55000 rows) sqlite> explain query plan select * from words where "word" glob 'a' || '%' || 'a'; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE words (~500000 rows) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Sunday, July 10, 2011 9:22 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] using index when using concatination Baruch Burstein <bmburst...@gmail.com> wrote: > I am using the following SQL statement for SQLite: > > select * from words where "word" like ? || '%' || ? ; > > In order to bind parameters to the first and last letters. I have tested > this both with and without an index on the column `word`, and the results > are the same. However, when running the queries as > > select * from words where "word" like 'a%a'; > > etc. (that is, hardcoding each value instead of using ||, the query is about > x10 faster when indexed. LIKE comparison may use the index only under certain very limited circumstances - see section 4 at http://sqlite.org/optoverview.html > Can someone show me how to use both the index and the parameters? Michael Black's suggestion should work: make it where word like ?; build the string of the form "a%b" in your application, and bind it to the parameter. Alternatively, you could try something like this: where word >= :first and word < :next and word like '%' || :last; Then bind :first as the first letter, :next as the character that immediately follows :first in the collation (e.g. if :first is 'a', then :next is 'b'), and :last is the last letter. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
