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
