我正在写一本字典应用程序,需要做的通常的文字提示,同时打字。
I'm writing a dictionary app and need to do the usual word suggesting while typing.
LIKE somestring%
是相当缓慢的(〜在〜10万行的表1300ms),所以我转向FTS3。
LIKE somestring%
is rather slow (~1300ms on a ~100k row table) so I've turned to FTS3.
但问题是,我还没有发现从字符串的开头来搜索一个理智的方式。 现在我执行查询像
Problem is, I haven't found a sane way to search from the beginning of a string. Now I'm performing a query like
SELECT word, offsets(entries) FROM entries WHERE word MATCH '"chicken *"';
,然后解析偏移字符串code。
, then parse the offsets string in code.
有没有更好的选择吗?
是,请务必设置索引字段字
和使用
Yes, make sure to set the index on field word
and use
word >= 'chicken ' AND word < 'chicken z'
替换LIKE或火柴或GLOB
instead of LIKE or MATCH or GLOB