SQLite fulltext virtual table normally usable?

Question

Even after reading a lot about the fulltext index of SQLite and a question arises that I didn't see answered anywhere:

I already have a table that I want to search with the fulltext index. I would just create an extra virtual table USING FTS3 or USING FTS4 and then INSERT my data into it.

Does that then use the double storage in total? Can I use such a virtual table just like a normal table and thus preventing storing the data twice?

(I am working with SQLite on Android but this question may apply to usage on any SQLite compatible platform.)

Selfrestraint

Answer 1

Despite the fact you did found some details I'll try to provide detailed answer:

1. Does that then use the double storage in total?

Yes it does. Moreover it might use event more space. For example, for widely known Enron E-Mail Dataset and FTS3 example, just feel the difference:

• The FTS3 table consumes around 2006 MB on disk compared to just 1453 MB for the ordinary table

• The FTS3 table took just under 31 minutes to populate, versus 25 for the ordinary table

Which makes the situation a bit unpleasant, but still full-text search worth it.

2. Can I use such a virtual table just like a normal table?

The short answer no, you can't. Virtual table is just a some kind of a View with several limitations. You've noticed several already.

Generally saying you should not use any feature which is seems to be unnatural for a View. Just a bare minimum required to let your application fully utilize the power of full-text search. So there will be no surprises later, with newer version of the module.

There is no magic behind this solution, it is just a trade-off between performance, required disk space and functionality.

Final conclusion

I would highly recommend to use FTS4, because it is faster and the only drawback is additional storage space needed.

Anyway, you have to carefully design virtual table taking into account a supplementary and highly specialized nature of such solution. In the other words, do not try to replace your initial table with the virtual one. Use both with a great care.

Update I would recommend to look through the following article: iOS full-text search with Core Data and SQLite. Several interesting moments:

• The virtual table is created in the same SQLite database in wich the Core Data content resides. To keep this table as light as possible only object properties relevant to the search query are inserted.
• SQLite implementation offers something Core Data does not: full-text search. Next to that, it performs almost 10% faster and at least 660% more (memory) efficiently than a comparable Core Data query.

Intonation

Answer 2

I just found out the main differences of virtual tables and it seems to depend on your usage whether a single table suffices for you.

• One cannot create a trigger on a virtual table.

• One cannot create additional indices on a virtual table. (Virtual tables can have indices but that must be built into the virtual table implementation. Indices cannot be added separately using CREATE INDEX statements.)

• One cannot run ALTER TABLE ... ADD COLUMN commands against a virtual table.

So if you need another index on the table, you need to use two tables.

Selfrestraint