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
