Normal Table vs Virtual Table SQLite DB

Question

Android developer manual seems to prefer FTS3 in SQLite DB when search is needed. I read the FTS3 description and it appears that it creates a virtual table instead of a permanent table. What's the difference between virtual table (FTS3) and normal table in SQLite? Is virtual table permanent and remains in the database even after I quit the app? Thank you.

Tomekatomes

Answer 1

A virtual table allows the SQLite engine to access the contents of a data store, using (usually) non-SQLite code. This allows the developer to add custom functionality to SQLite.

In the case of FTS, it was originally not a part of the SQLite engine. It was code that was external to SQLite, that allowed the end user to do a full text search on data.

Is a virtual table permanent? That depends on the implementation. For FTS the data is permanent. However, you could create an implementation that uses RAM for storage - this obviously would disappear when the application is terminated.

More on virtual tables: http://www.sqlite.org/vtab.html

Terrify

Answer 2

I think it's more accurate to say that the API guide for creating a search interface notes that FTS3 does full-text search much faster than LIKE, depending on the characteristics of the search. For example, a regular SQLite query using LIKE allows wildcards, whereas an FTS3 query using MATCH does not. If you want to search for tokens, you may want to go with FTS3. If you're doing imprecise match, you definitely have to go with LIKE.

Oxysalt

I think wildcards are allowed when using MATCH too. You just need to put * based on what I read on SQLite website. – Tomekatomes