How to improve sqlite like statement performance

Indexes and like don't get along in most databases. The best bet is to rewrite the query as a range query, if possible, because the index will then be used:

select *from wordIndexwhere word between 'test acces' and 'test acces{'

(The open brace is the ASCII character immediately following 'z'.)

If you are looking for patterns at the beginning of a word (say '%test'), then you may have to resign yourself to a full table scan.

EDIT:

Indexes and like *do` get along nowadays in most databases when the patterns starts with a constant, so you can do:

select *from wordIndexwhere word like 'test acces%' ;

I'm not 100% sure about SQLite, though, so check the execution plan to see if it uses the index.

Try this:

SELECT * FROM wordIndexWHERE word COLLATE NOCASE BETWEEN @SearchString AND @SearchString || '~~~~~~~~~~'

"~" is the biggest ASCII symbol.

I will have a slightly different answer than Gordon Linoff, but with the same approach.

If you want to keep any characters that follow 'test acces' you should try this:

SELECT * FROM wordIndexWHERE word > 'test acces' AND word < 'test accet';