Room and Full-Text Search

SQLite supports FTS virtual tables for full-text searching of content. As the SQLite documentation puts it:

The most common (and effective) way to describe full-text searches is “what Google, Yahoo, and Bing do with documents placed on the World Wide Web”.

Originally, Room did not have any support for FTS, but in 2019 Google added FTS support to Room 2.2.0. So, in current versions of Room, you can use FTS in your Android app, for rich queries of long pieces of text.

In this chapter, we will explore more about this capability.

What Is FTS?

Standard SQL databases are great for ordinary queries. In particular, when it comes to text, SQL databases are great for finding rows where a certain column value matches a particular string. They are usually pretty good about finding when a column value matches a particular string prefix, if there is an index on that column. Things start to break down when you want to search for an occurrence of a string in a column — “find all rows where the column prose contains the word vague” — as this usually requires a “table scan” (i.e., iteratively examining each row to see if this matches). And getting more complex than that is often impossible, or at least rather difficult.

SQLite, in its stock form, inherits all those capabilities and limitations. However, SQLite also offers full-text indexing, where we can search our database much like how we use a search engine (e.g., “find all rows where this column has both foo and bar in it somewhere”). While a full-text index takes up additional disk space, the speed of the full-text searching is quite impressive.

A Word About SQLite Versions

SQLite has evolved since Android’s initial production release in 2008.

In many cases, Android does not incorporate updates to third-party code, for backwards-compatibility reasons (e.g., Apache’s HttpClient). In the case of SQLite, newer Android versions do take on newer versions of SQLite… but the exact version of SQLite that a given version of Android uses is undocumented. Worse, some device manufacturers replace the stock SQLite for a version of Android with a different one.

This Stack Overflow answer contains a mapping of Android OS releases to SQLite versions, including various “anomalies” where manufacturers have elected to ship something else.

In many cases, the SQLite version does not matter. Core SQLite capabilities will have existed since the earliest days of Android. However, full-text indexing did not exist in the first SQLite used by Android, meaning that you will have to pay attention to your minSdkVersion and aim high enough that devices should support the full-text indexing option you choose.

Note that you could use an external SQLite implementation, one that gives you a newer SQLite engine than what might be on the device. For example, SQLCipher for Android ships its own copy of SQLite (with the SQLCipher extensions compiled in), one that is often newer than the one that is baked into the firmware of any given device.

FTS3, FTS4, and FTS5

There are three full-text indexing options available in SQLite: FTS3, FTS4, and FTS5. Google, with Room, offers support for FTS3 and FTS4, not FTS5. Presumably, Google has determined that few device manufacturers enable FTS5. If you package your own SQLite version, you could ensure that FTS5 is available to you. Perhaps in the future, Google will add FTS5 support.

Comparing FTS3 and FTS4, FTS4 can be much faster on certain queries, though overall the speed of the two implementations should be similar. FTS4 may take a bit more disk space for its indexes, though.