Hello ! Thanks for reply !
I know what you are saying but I brought this to discussion because I saw several commits on sqlite3 to address better index usage and this I think is one more case that could be considered by sqlite3, even been a case sensitive comparison at first, it'll be a lot cheaper to scan using the index and then decide if the record is valid by comparing it case sensitive afterwards. Cheers ! > Thu Mar 17 2016 07:24:46 PM CET from "Clemens Ladisch" ><clemens at ladisch.de> Subject: Re: [sqlite] Sqlite do not use index collate >nocase > > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?