Implementing Global Search across Tables

In this lesson, you will learn how to design a unified search index to enable lightning-fast, cross-table querying in mobile applications.

The Challenge of Distributed Data

In a typical mobile application, data is fragmented across different tables—for instance, Contacts, Messages, and Notes. If a user performs a global search, running SELECT statements across three separate tables and merging the results in memory is inefficient. This approach is slow, battery-intensive, and makes pagination nearly impossible.

The Search Index Pattern

Instead of searching raw tables, we implement a Unified Search Index table. This is a dedicated, flattened table that acts as a "source of truth" for searchable keywords. When data is created or updated in your core tables, you trigger an insertion into this index.

Your index table (global_search_index) should look like this:

CREATE TABLE global_search_index (
    row_id INTEGER,
    table_name TEXT,
    search_term TEXT,
    snippet TEXT,
    PRIMARY KEY(table_name, row_id)
);

-- Creating a covering index for the search term
CREATE INDEX idx_search_term ON global_search_index(search_term);

Why This Architecture Works

1. Single Query Execution: You replace multiple table scans with a single SELECT from the global_search_index.
2. Normalized Retrieval: Since the index stores the row_id and table_name, you can easily fetch the full object details from the original tables once the user selects a result.
3. Performance: Because this table is optimized specifically for string matching (and potentially FTS5 extensions in SQLite), it keeps your UI responsive without scanning large blobs or unrelated columns.

Keeping the Index Fresh

To ensure your search results don't become stale, you should use database triggers or a transaction-based wrapper in your application logic. Every time an entry in Contacts is updated, your data layer should simultaneously update the corresponding record in global_search_index. This keeps the "search space" lightweight and performant, which is crucial for preserving battery life on mobile devices.

Key Takeaway: Rather than querying multiple tables concurrently, maintain a flattened, specialized index table to ensure global search remains performant and energy-efficient.