How to improve sqlite like statement performance

Question

I create a table use such schema:

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

Now the table have about 450,000 row records.When I use Like statement Below on ipod4, the performance is not good: select * from wordIndex where word like 'test acces%'; Use explain output:

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|

May be I need build an additional inverted index to improve the performance or ...? Thanks advance!

Are you using only this pattern for LIKE (i.e. starts with)? — Hamlet Hakobyan, Dec 27 '12 at 15:07

Gordon Linoff · Accepted Answer · 2016-01-26T12:18:51.120

15

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 wordIndex
where 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 wordIndex
where word like 'test acces%' ;

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

edited Jan 26 '16 at 12:18

answered Dec 27 '12 at 15:11

Gordon Linoff

1,242,037
58
646
786

1

Thanks Gordon Lionff, it do readlly work. Before query rewrite : Execution Time: 1.756531 second, after query rewrite:Execution Time: 0.011285 on my iPod Touch 4 . – ericfang Dec 27 '12 at 15:23
Tnx but it's case-sensitive ! – alizx Jan 26 '16 at 05:46
One can also use unicode character ``. To print it use `python -c 'print u"\uffff"'`. – wieczorek1990 Jun 28 '18 at 10:21

score 5 · Answer 2 · answered Dec 27 '12 at 15:16

5

Try this:

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

"~" is the biggest ASCII symbol.

answered Dec 27 '12 at 15:16

Hamlet Hakobyan

32,965
6
52
68

score 2 · Answer 3 · answered Jan 18 '13 at 13:13

2

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 wordIndex
WHERE word > 'test acces' AND word < 'test accet';

answered Jan 18 '13 at 13:13

Stephane D.

142
7

How to improve sqlite like statement performance

3 Answers3