FTS5 external content tables
(1) By gabrielepx on 2021-07-17 22:19:39 [link] [source]
Hello, until now I've used FTS3 and I'm finally moving to FTS5. I've already modified my software to use it, but I'm wondering about FTS5 external content tables. Are them just a way to save storage space or can I get speed improvements as well? I always joined the FTS3 table with the real table to get the additional columns not indexed in the FTS table. Using a single FTS5 table with external content and all the additional columns referenced to the normal one (with UNINDEXED) will I have an additional advantage? I'm talking about tables with 200-5000 rows (not expected to exceed 20000 rows for the foreseeable future) and used in web (PHP) applications. Thanks in advance for any suggestion.
For such a small collection of data my hunch is that it won't matter either way. I recommend trying out implementing both patterns and running some micro-benchmarks though to see for yourself - doing that is always a useful exercise for this kind of performance question.