Android search : Use FTS tables along with normal SQLite DB Tables

Question

This is more of a design question - not just a coding question.

I already have an app that stores data in SQLite DB tables. Now I want to add Search feature into this.

I understand that in order to enable search feature I need FTS tables.

The tables that I have are (not exactly the same but giving very similar example):

Content table - has columns like: Id, Title, Content, Created By, Created On, Changed By, Changed on etc ...

Comments table - has columns like: Id, Comments, Created By, Created On, In Reply to etc ...

Other tables like User Data, Meta Data, Categories, Tags etc.

Here I only want search capability on Title, Content and Comments. Obviously I don't need search capability on other columns like created by etc.

Which is the best option ?

1. Should I discard my old tables and create ONLY FTS table ?
2. Should I continue to use old tables and create new FTS tables that only support the search feature.

Problems that I see with option 1 are:

• It is a disruptive change !
• I have a set of multiple tables with many non-text columns on which I do not intend to perform search. How do I model them in FTS ?
• Right now I am utilizing the features of RDBMS like Join etc which I think I wont be able to do if I switch fully to FTS tables !

Problems that I see with option 2 are:

• It will result in duplicate tables - and will consume more memory / space !
• Every time I insert / update / modify a entry in my SQL tables, I have to do the same change in the FTS tables.

So, what is the best option ? Has any body faced similar challenges ?

Answer 1

FTS tables cannot be efficiently queried for non-FTS searches, so option 1 is out.

The entire FTS table essentially is an index. This is a speed/space tradeoff, and the ability to do full-text searches usually is worth it. To avoid storing the original text twice, use external content tables.

To keep the original and FTS tables in sync, use triggers.