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
