The column data is case sensitive and the comparison is case sensitive. There is no case sensitive index.
You either need (a) to make the column collate nocase (in which case any index on the column is also in the same nocase collation and thus you do not need it in the create index), or (b) specify that the comparison is using collation nocase. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte > Sent: Thursday, 17 March, 2016 12:06 > To: SQLite mailing list > Subject: [sqlite] Sqlite do not use index collate nocase > > Hello ! > > In one database I created an index with collate nocase but it seems that > sqlite do not recognize it as a candidate index for a select. > > Bellow is the schema, the real database has more than a million rows. If I > alter the table to include the collation or remove the collation from the > index then sqlite does use the index. > > I expect an index collate nocase to be valid to queries like the one > bellow. > > > Cheers ! > > Schema: > > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE 'items' ( > ??? 'id' integer PRIMARY KEY, > ??? 'parent' INTEGER, > ??? 'by' text, > ??? 'score' integer DEFAULT 0, > ??? 'title' text, > ??? 'type' text, > ??? 'url' text, > ??? 'deleted' BOOLEAN DEFAULT 0, > ??? 'dead' BOOLEAN DEFAULT 0, > ??? 'comment' TEXT DEFAULT null, > ??? 'time' integer NOT NULL > ); > CREATE INDEX "items_user_idx" ON "items"("by" COLLATE NOCASE); > COMMIT; > > explain query plan select * from items? where by='doppp'; > > SCAN TABLE items > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
