On 2016/03/17 8:06 PM, Domingo Alvarez Duarte wrote:
> 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
Quite correctly, the Query planner (or SQL engine) cannot simply assume
that you want to select items without case, why would it assume that?
There is nothing in your Table definition that suggests the "by" column
is a NOCASE column, nor does the SELECT query say to find items using
NOCASE collation logic. That means it cannot and must not assume that
you want to ignore case, so it cannot use an index that does not
recognize case.
Try either making the column NOCASE collated, or at least specify NOCASE
for the SELECT comparison (both of which are documented) to let the QP
know that you are intending it to ignore case when running this query,
and then it may (and will) use the NOCASE index.
