Android.SQLite – Optimize SELECT query with LIKE

For the last 2 days I was trying to optimize my SELECT query with LIKE operator. Even I tried to use FTS tables. It was fast, but it did not return the result as I intended. So I tried another way

I have this table:

CREATE TABLE meaning(
KEY TEXT,
VALUE TEXT,
entries BLOB);

Then created INDEX on key column:

CREATE INDEX index_key ON meaning(KEY);

Then I was getting data using this query:

SELECT rowid AS _id, VALUE, entries
FROM meaning
WHERE KEY
LIKE 'dog%'
ORDER BY KEY
LIMIT 100

Result was coming in 510ms. I tried to optimize my select query. Actually, this was not because of SELECT query. It was because of INDEX(index_key). I found that LIKE is not case sensitive so logical step is to make INDEX not case sensitive. It was done in this way:

CREATE INDEX index_key ON meaning (KEY COLLATE NOCASE);

Now same query as stated before, is returning result in 83ms which means more than 6 times faster. In applications, like a dictionary which required incremental search which difference is enormous.