Some time ago I had to implement an Android dictionary application for one of my customers. Of course that dictionary application's main feature was supposed to be its search capability. Using SQL "LIKE" queries was not an option as the dictionary data set was pretty big and had to provide full-text searches not even for words but also for their textual descriptions.
As a consequence, I had to do some research on full-text search capabilities in Android. While reading through the dev guide on search dialogs, a sentence triggered my attention :
If your data is stored in a SQLite database on the device, performing a full-text search (using FTS3, rather than a LIKE query) can provide a more robust search across text data and can produce results significantly faster.
FTS3? That sounded interesting and I did some research into that direction.
The FTS3 SQLite Extension
FTS is the acronym for "full-text search". FTS3 itself is an SQLite extension that is now part of SQLite that provides support for creating virtual tables which in fact maintain an inverted index for full-text searches. That means you can use FTS tables to enable full-text search in Android applications.
Let us see how this was done in the dictionary application. The DatabaseOpenHelper class extended SQLiteOpenHelper and created (among others) a virtual table.
creates a virtual table using the FTS3 extension. The table can be filled and updated with the usual SQL commands but uses an inverted index for fast full-text searches underneath.
The interesting part is the query syntax. FTS3 tables are queried using the MATCH keyword.
As can be seen from the example above, the FTS3 supports not even unqualified queries like
but also qualified queries identifying the query columns by column: columnName
and the * operator
A complete guide on FTS full-text queries can be found at .