SQLite Forum

SELECT optimization for constant expression
Create the index with COLLATE NOCASE. Only then will LIKE use the index, because LIKE is by default case insensitive. The CREATE INDEX I would have used for my example (not shown) would be like this.

CREATE INDEX df_players_surname ON df_players(surname COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM df_players WHERE surname LIKE 'Dennis';