Room and Full-Text Searching

SQLite supports FTS virtual tables for full-text searching of content.

Room does not.

NOTE: The upcoming 2.1.0 version of Room will support FTS. That edition of Room is part of AndroidX; this book focuses on the original android.arch edition of Room, which pre-dates this FTS support.

However, there are ways to get FTS support in a Room-managed database, but it requires you to do more of the work yourself. In this chapter, we will explore how to make this work, while also traveling in time. Or perhaps just reading about somebody who travels in time.

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.

There are a few full-text indexing options available in SQLite: FTS3, FTS4, and FTS5. Newer versions (higher numbers) are generally faster but take more disk space. FTS3 has been around since the beginning of Android. FTS4 arrived with the version of SQLite used in API Level 11. FTS5, by constrast, is only likely to be available on API Level 24 and higher devices.

This chapter does not cover all of the details of using FTS with SQLite. For that, please see the “Advanced Database Techniques” chapter in The Busy Coder’s Guide to Android Development and the SQLite FTS documentation. This chapter is focused solely on enabling this stuff from Room, though you will see a bit of how FTS works along the way.