Should LIKE 'searchstr%' use an index?

Question

I have a database with several fields :

word_id — INTEGER PRIMARY_KEY
word — TEXT
...

And ~150k rows. Since this is a dictionary, I'm searching for a word with mask 'search_string%' using LIKE. It used to work, taking 15ms to find matching rows. The table has an index for a field 'word'.

I modified the table (some fields which are out of the scope) and it's taking 400ms to execute a query, so I understand that as it fails to use index now. Straightforward query with = instead of LIKE shows 10ms result. What's happening here?

Answer 1

An index cannot safely be used in this case. A naive implementation would transform this:

... WHERE word LIKE 'search_string%'

into

... WHERE word >= 'search_string' AND word < 'search_strinh'

by incrementing the last character of the search string. The greater-than and less-than operators can use an index, where LIKE cannot.

Unfortunately, that won't work in the general case. The LIKE operator is case-insensitive, which means that 'a' LIKE 'A' is true. The above transformation would break any search string with capitalized letters.

In some cases, however, you know that case sensitivity is irrelevant for a particular column, and the above transformation is safe. In this case, you have two options.

1. Use the NOCASE collating sequence on the index that covers this particular field.
2. Change the behavior of the LIKE operator program-wide by running PRAGMA case_sensitive_like = ON;

Either of these behaviors will enable SQLite to transparently do the above transformation for you; you just keep using LIKE as always, and SQLite will rewrite the underlying query to use the index.

You can read more about "The LIKE Optimization" on the SQLite Query Optimizer Overview page.