How to improve sqlite like statement performance

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!

Answers


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.


Need Your Help

Spring Data Project - Couchbase Integration

java spring spring-data couchbase

I went over the below tutorials for using couchbase db via Spring

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.