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?

Thanks.

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;