SQLite Forum

Quick lookup of like terms in FS5 index

Quick lookup of like terms in FS5 index

(1) By Mike Bailey (bbtmike) on 2020-04-07 17:56:53 [link] [source]

I'm trying to implement an autocomplete type feature for a user entering terms to search an FTS5 index with. If they type in "hous", I'd like to list terms with that prefix:

house housed housing

I have setup the fts5vocab table and the following query gets me the results I want:

select * from test_vocab where term like 'hous%'

However, this query takes about 12 seconds to run with my large index. Querying the index for results on the same terms is near instant:

select * from testindex where body MATCH 'hous*'

Is there a way to query the terms in the index so that it performs closer to an actual FTS index query?


(2) By Dan Kennedy (dan) on 2020-04-08 15:12:11 in reply to 1 [source]

This should be faster:

  SELECT * FROM test_vocab WHERE term>='hous' AND term<'hout';

or just:

  SELECT * FROM test_vocab WHERE term GLOB 'hous*';

Or your LIKE query will probably also be fast if you turn on case-sensitive LIKE first:

  PRAGMA case_sensitive_like = ON;

(3) By Mike Bailey (bbtmike) on 2020-04-08 16:34:43 in reply to 2 [link] [source]

That did the trick.