OK, the index IS being used with ... where myCol LIKE 'foo%' ... but only when I execute the query using the SQLite3.exe command-line utility. But the index is not being used when the query is executed via three different front-ends to SQLite, all of which must be doing something to confound the index such as compiling the prepared statement with sqlite3_prepare().
Do I understand the docs correctly, that if the query statement contains a literal string (as distinct from a bound parameter) it doesn't matter which function is used to compile the statement and the index WILL be used provided all of the optimization prerequisites are met? <docs>The LIKE optimization is not attempted if the right-hand side is a parameter <http://www.sqlite.org/lang_expr.html#varparam> and the statement was prepared using sqlite3_prepare() <http://www.sqlite.org/c3ref/prepare.html> or sqlite3_prepare16() <http://www.sqlite.org/c3ref/prepare.html>. </docs> Thanks Tim Igor Tandetnik wrote: > Tim Romano wrote: > >> Thanks for the correction, Pavel, about the mixed comparison mode >> (ASCII-range: case-insensitive, above-ASCII: case-sensitive). >> >> I've added a column to my table: >> >> ALTER TABLE WORDS >> ADD COLUMN spell varchar COLLATE NOCASE >> >> and have then copied the contents of a 100% pure ASCII column into >> column SPELL. >> >> explain query plan >> select * from WORDS where spell like 'foo%' >> >> shows that SQLite is still doing full table scan. >> > > Did you create an index on that new column? > > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.425 / Virus Database: 270.14.67/2506 - Release Date: 11/16/09 > 07:43:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
