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
