How to increase work efficiency with sqlite

Question

I am creating a table using a schema like this:

CREATE TABLE wordIndex(id integer primary key, word varchar(128), offset integer, length integer); CREATE INDEX word_idx on wordIndex(word); 

The table now contains about 450,000 row entries. When I use the Like operator below on ipod4, the performance is not very good: select * from wordIndex, where a word like "test acces%"; Use the output of the explanation:

 explain select * from wordIndex where word like 'test acces%'; 0|Trace|0|0|0||00| 1|Goto|0|16|0||00| 2|OpenRead|0|2|0|4|00| 3|Rewind|0|14|0||00| 4|String8|0|2|0|test acces%|00| 5|Column|0|1|3||00| 6|Function|1|2|1|like(2)|02| 7|IfNot|1|13|1||00| 8|Rowid|0|4|0||00| 9|Column|0|1|5||00| 10|Column|0|2|6||00| 11|Column|0|3|7||00| 12|ResultRow|4|4|0||00| 13|Next|0|4|0||01| 14|Close|0|0|0||00| 15|Halt|0|0|0||00| 16|Transaction|0|0|0||00| 17|VerifyCookie|0|2|0||00| 18|TableLock|0|2|0|wordIndex|00| 19|Goto|0|2|0||00| 

Maybe I need to create an additional inverted index to improve performance or ...? Thanks in advance!

Answer 1

Indexes and like do not work in most databases. It is best to rewrite the query as a range query, if possible, because then the index will be used:

 select * from wordIndex where word between 'test acces' and 'test acces{' 

(The open brace is an ASCII character immediately after "z".)

If you are looking for patterns at the beginning of a word (for example, "% test"), you may need to surrender yourself in a full table scan.

EDIT:

Indexes and like * do` go today in most databases when templates start with a constant, so you can do:

 select * from wordIndex where word like 'test acces%' ; 

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

Answer 2

Try the following:

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

"~" is the largest ASCII character.

Answer 3

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

If you want to keep the characters that follow "test acces", you should try the following:

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