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. QUESTION: Is it possible to confirm that the column actually has been created with NOCASE collation? PRAGMA table_info(WORDS) doesn't show the collation sequence for the column, and PRAGMA collation_list lists the names of sequences for the current connection but does not associate them with a column. Thanks sqlite3_prepare_v2() <http://www.sqlite.org/c3ref/prepare.html> or sqlite3_prepare16_v2() <http://www.sqlite.org/c3ref/prepare.html>. <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> </docs> Pavel Ivanov wrote: >> collation-sequence is default NO-CASE >> > > Default collation is BINARY. So either yours is default or NOCASE - not both. > > >> A question, however, on the Latin-1, ASCII range requirement: this is a >> column requirement and not a database requirement, correct? >> > > It's not a requirement at all. It's just the fact that LIKE will > compare ASCII characters case-insensitive and all other characters > case-sensitive when case-sensitive comparison is off. > > Pavel > > On Mon, Nov 16, 2009 at 7:47 AM, Tim Romano <tim.rom...@yahoo.com> wrote: > >> After reading http://www.sqlite.org/optoverview.html, I think my query >> meets the requirements for index use with the LIKE operator: >> >> The column is varchar(75) and so TEXT affinity. >> The column uses Latin-1 characters exclusively. >> The wildcard appears at the far right end of the string literal, e.g. >> myColumn LIKE 'foo%' >> The escape clause does not appear. >> Case-sensitivity=false; >> collation-sequence is default NO-CASE >> >> QUESTION: >> A question, however, on the Latin-1, ASCII range requirement: this is a >> column requirement and not a database requirement, correct? I have >> several columns with text affinity; one is strict ASCII and represents >> characters outside the ASCII range as html-entities (e.g. "ΓΌ") and >> the others store the unicode characters. The database encoding is UTF-8. >> >> My query with the LIKE operator worked instantaneously in MS-Access, >> BTW, where I originally had the database. After exporting to delimited >> text and reimporting into SQLite, most queries in SQLite are just as >> fast, executing in under a second. But this query with the LIKE operator >> takes 40 seconds because of the full-table scan. >> >> Thanks >> >> >> _______________________________________________ >> 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 > ------------------------------------------------------------------------ > > > 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
