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?

Answer 1

An index can be used to speed up a search only if it uses the same collation as the query.

By default, an index takes the collation from the table column, so you could change the table definition:

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

However, this would make the first query slower.

To speed up both queries, you need two indexes, one for each collation. So to use the default collation for the implicit index, and NOCASE for the explicit index:

CREATE TABLE IF NOT EXISTS Streams
(
    Name      char(40) NOT NULL COLLATE NOCASE,
    GlobalVer INTEGER  NOT NULL,
    PRIMARY KEY(Name, GlobalVer)
) WITHOUT ROWID;
CREATE INDEX IF NOT EXISTS Streams_nocase_idx ON Streams(Name COLLATE NOCASE, GlobalVar);

(Adding the second column to the index speeds up the ORDER BY in this query.)