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
>
>
>
?
