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?