CODEWITHSUNDEEP
androiddatabasesqlitefull-text-searchandroid-sqlite
nithesh1990
nithesh1990

Reputation: 51

SQLite: Normal Table vs Virtual Table. Which one to choose?

I am developing an android application and it uses Sqlite DB for storing and retrieving data. The application has a feature of storing and retrieving text data. I have decided to go with VIRTUAL Table Implementation because of the following benefits

a. Faster Execution

b. Full text search

because my application should be scalable to save large amounts of text data and should be able to search for a particular text quickly. Before starting i have few questions.

  1. Should i go with having only virtual table ? Is it safe for long run ? I read that it is safe to keep a normal table and a virtual table. I don't think it's a good idea to keep 2 copies of same data
  2. I am planning to index some columns in virtual table to improve search efficiency ? Does this cause any performance and memory overhead ?
  3. I have 7 columns in the table and the data to be searched is spread across 4 columns. Is it good to go with virtual table or a normal table indexing those 4 columns ?

Thanks in advance

Upvotes: 0

Views: 2083

Answers (1)

CL.
CL.

Reputation: 180020

An FTS table is not really a table; think of it as an index.

To avoid storing tha same data twice, use an external content table.

FTS tables store all data as text, and do not allow any other index than the built-in full-text index. Furthermore, the documentation says:

FTS tables can be queried efficiently using SELECT statements of two different forms:

  • Query by rowid. If the WHERE clause of the SELECT statement contains a sub-clause of the form "rowid = ?", where ? is an SQL expression, FTS is able to retrieve the requested row directly using the equivalent of an SQLite INTEGER PRIMARY KEY index.
  • Full-text query. If the WHERE clause of the SELECT statement contains a sub-clause of the form " MATCH ?", FTS is able to use the built-in full-text index to restrict the search to those documents that match the full-text query string specified as the right-hand operand of the MATCH clause.

If neither of these two query strategies can be used, all queries on FTS tables are implemented using a linear scan of the entire table.

So you should never try to store non-text data in an FTS table.

Upvotes: 2

Related Questions