How to properly create and use non full-text search index for the FTS5 virtual table?

53 Views Asked by At

I created an FTS5 virtual table for a full-text search like that

CREATE VIRTUAL TABLE IF NOT EXISTS search_table USING FS 5(user_id, date UNINDEXED, text);

I don't want to use the date column as a search constraint, so I marked it UNINDEXED.
Also, I want to speed up the search by using user_id column because I usually need to look for a "phrase" in a user's context. But user_id will be indexed and tokenized for the full-text search. And it will require additional memory for that.

The questions are:
- how to properly define the `user_id` column to avoid full-text search indexing but use it like an ordinary DB index?
- can I `CREATE INDEX` for `user_id`?
- would `SELECT` consider this index restriction in search?

0

There are 0 best solutions below