7

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 ?

Varun Verma
  • 542
  • 4
  • 17

1 Answers1

12

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.

CL.
  • 173,858
  • 17
  • 217
  • 259