Run, SQL Run!

outubro 20, 2026

Searching data like products, messages or emails is one of the most common features in modern applications. There are couple of ways to implement this. In many cases, the client works together with backend services for searching. But, for fully client based or offline first app without network connection, we must execute search operations locally. When the number of tables and rows grows, local queries can become slow. To handle this performance issue, a widely used technique is full text search. Our goal is to achieve the same search functionality that exists in current Gmail application.

About The Demo

It is demonstrated in a sample email application, which displays only emails with sender, subject, and body. After performing a search, it is possible to see the number of results, the average time taken, and the times from all five trials. Also note that while FTS searches every content in every field, a query using LIKE only searches the beginnings of tields, so the results are not exactly the same. FTS generally finds more matches.

How Does FTS Work Generally?

There are two phases in full text search: indexing and searching. Full text search uses a virtual table to index text columns for faster search. While indexing, the text is split into searchable tokens, and each token is linked with its source row through a special column called docid, which points to the original document in the normal table. After the index is generated, users can search, and the system processes the query and uses index to locate documents that contain the matching keywords.

FTS4 Usage With Room

Room library was used in this demo, which provides built in support, and the setup is easy to do. Firstly, we must have a normal table.

Then we can add the FTS version of it by providing FTS4 annotation with required properties in FTS table. For our case I added all of these.

In this demo, the LIKE and query% searches we perform only check the beginning of the content. For example, a search for “com”:

Finds the line “Communication is important for…”
but cannot find the line “I have an idea to improve the communication.”

On the other hand, FTS can match both of these examples. Therefore, we can write our DAOs as follows.

Calculation

To get the average duration of the queries, we can write a top-level function that sends 5 requests and calculates the average time.

Since the requests are sent consecutively, the results may be coming from cache. No additional improvement was implemented to prevent this.

Before moving on to the results, I used a database consisting of 20,776 items, and the data was provided via ChatGPT, Claude, and Gemini.

Result

The results are shown below. The three visuals include the outputs of the queries “sale,” “tomorrow,” and “rep.”

Demo app comparing LIKE and FTS4 search performance for “sale”, “tomorrow”, and “rep”.

As expected, FTS returns more results because it evaluates all the words in the sentence. The LIKE query we wrote, on the other hand, only checks the beginning of the text. Additionally, as can be seen from the results, FTS retrieves more matches faster.

References

Run, SQL Run! was originally published in ProAndroidDev on Medium, where people are continuing the conversation by highlighting and responding to this story.