The wording of my question comes from a comment at the end of the blog post Android Quick Tip: Using SQLite FTS Tables. As the title implies, the post tells how to create and query full text search virual tables in your android app. The comment by user Fer Raviola specifically reads
my question is why dont' we ALWAYS use FTS tables!. I mean, they ARE faster
The blog author did not reply (at the time of this writing anyway), but I thought it was an interesting question that deserves an answer. After all, FTS tables can be made for an entire table, not just a specific text column. At first look, it seems like it would both simply and speed up queries.
One could also completely do away with the non-virtual table. That would eliminate having to keep the virtual and non-virtual tables in sync with triggers and external content tables. All the data would be stored in the virtual table.
@CL. says that this is not a good option, though, because "FTS tables cannot be efficiently queried for non-FTS searches." I assume that this has something to do with what the SQLite documentation says here:
-- The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);
SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH 'sqlite'; -- Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH 'search'; -- Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Slow. Linear scan.
SELECT * FROM mail WHERE subject = 'database'; -- Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH 'database'; -- Fast. Full-text query.
But are the slow queries really so much slower than if one were just doing a normal query on a normal table? If so, why?
Here are some general potential downsides that I can think of to only using a virtual FTS table in Android:
- The table would be larger because of the size that the index takes.
- Operations like INSERT, UPDATE, and DELETE would be slower because the index would have to be updated.
But as far as queries themselves go, I don't see what the problem would be.
Update
The Android documentation example Storing and Searching for Data uses only an FTS virtual table in its database. This seems to confirm that there are at least some viable options for FTS only databases.
