SQLite Forum

FTS5 external content tables
Login

FTS5 external content tables

(1) By gabrielepx on 2021-07-17 22:19:39 [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.

(2) By Simon Willison (simonw) on 2021-07-20 00:00:49 in reply to 1 [link] [source]

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.