Domingo Alvarez Duarte wrote: > 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. > > CREATE TABLE 'items' ( > 'by' text, [...] > ); > CREATE INDEX "items_user_idx" ON "items"("by" COLLATE NOCASE); > > explain query plan select * from items where by='doppp'; > SCAN TABLE items
This is a case-sensitive comparison, so the index cannot be used. The query has to use a case-insensitive comparison: explain query plan select * from items where by like 'doppp'; SEARCH TABLE items USING INDEX items_user_idx (by>? AND by<?) explain query plan select * from items where by='doppp' collate nocase; SEARCH TABLE items USING INDEX items_user_idx (by=?) Regards, Clemens