What is the advantage of FTS over custom solution?

Question

I have a biggish database ~32mb which has lots of text in 4 languages. Including Arabic and Urdu. I need to search this text in the most efficient way (speed & size).

I am considering FTS, and trying to find out how to implement it. Right now I am reading http://www.sqlite.org/fts3.html#section_1_2 about it.

It seems to me, an FTS table is just like a normal table used to index all the different words. So my questions are:

1) If to populate FTS I have to do all the inserts myself, then why not make my own indexed word table, what is the difference?

Answer : Yes there are many advantages, many built in functions that help. For example with ranking etc, searching of stems and the transparent nature of how it all works in android makes the FTS approach more appealing.

2) On the google docs I read its a virtual in memory table, now this would be massive right... but it doesnt mention this on the SQLite website. So which is it?

3) Is there an easy way to generate all the different words from my columns?

4) Will the FTS handle arabic words properly?

Answer 1

1. FTS allows for fast searching of words; normal indexes only allow to search for entire values or for the beginning of the value. If you table has only one word in each field, using FTS does not make sense.
2. FTS is a virtual table, but not an in-memory table.
3. You can get individual terms from the full-text index with the fts4aux table.
4. The default tokenizer works only with ASCII text. You have to test whether the ICU or UNICODE61 tokenizers work with your data.