I have a database where the main operation is read (once a month all entries will be dropped and re-written).
All fields are type TEXT so I use FTS5 and it works, except when I run a JOIN query. Tables monographs (books, 3.5M entries) and authors (1.5M entries) are cloned into virtual tables using FTS5. All "single" queries against these virtual tables work but I need to keep the normal tables because a joining query between monographs and authors takes much longer than with normal tables.
Query plan normal tables:
Query plan virtual tables:
I know that is because lack of indexes. I reserve the normal tables for this purpose but that is not ideal because quality of the FTS5 search is much better. I'd like same performance as with normal tables using virtual, so I'll only need virtual tables.