SQLite speed up select with collate nocase

Question

I have SQLite db:

CREATE TABLE IF NOT EXISTS Commits
(
    GlobalVer INTEGER PRIMARY KEY,
    Data blob NOT NULL
) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS Streams
(
    Name char(40) NOT NULL,
    GlobalVer INTEGER NOT NULL,
    PRIMARY KEY(Name, GlobalVer)
) WITHOUT ROWID;

I want to make 1 select:

SELECT Commits.Data
  FROM Streams JOIN Commits ON Streams.GlobalVer=Commits.GlobalVer
 WHERE 
    Streams.Name = ?
ORDER BY Streams.GlobalVer
LIMIT ? OFFSET ?

after that i want to make another select:

SELECT Commits.Data,Streams.Name
  FROM Streams JOIN Commits ON Streams.GlobalVer=Commits.GlobalVer
 WHERE 
    Streams.Name = ? COLLATE NOCASE
ORDER BY Streams.GlobalVer
LIMIT ? OFFSET ?

The problem is that 2nd select works super slow. I think this is because COLLATE NOCASE. I want to speed up it. I tried to add index but it doesn't help (may be i did sometinhg wrong?). How to execute 2nd query with speed approximately equals to 1st query's speed?

SQLite speed up select with collate nocase

1 Answers