Room database indices in 2026: finding the query that's actually slow and fixing it

A Room-backed app that felt instant in testing can start stuttering months later, once a real user has a thousand transactions instead of the twelve you seeded it with. The usual instinct is to add an index somewhere and hope. That works about as often as it doesn’t, because indices don’t speed up a table — they speed up a specific access pattern, and the wrong one adds write overhead for zero benefit. Here’s how to find the query that’s actually slow, confirm it, and index it correctly.

Don’t guess — measure

SQLite tells you exactly how it plans to run a query if you ask. Prefix any query with EXPLAIN QUERY PLAN and run it through adb shell or a raw Room query:

@RawQuery
fun explain(query: SupportSQLiteQuery): List<ExplainRow>

EXPLAIN QUERY PLAN
SELECT * FROM transactions WHERE category_id = 7 ORDER BY date DESC;

The output line is the whole story. SCAN transactions means SQLite is reading every row in the table and checking the condition on each one — the cost grows linearly with table size. SEARCH transactions USING INDEX idx_transactions_category (category_id=?) means it jumped straight to the matching rows. Everything about indexing comes down to turning SCAN into SEARCH for the queries you actually run often, and leaving everything else alone.

The mistake to avoid here is indexing based on which column feels important. A notes column rarely gets filtered on; a category_id used in every list screen’s WHERE clause is a completely different story. Run EXPLAIN QUERY PLAN on your five or six hottest DAO queries before touching anything — the ones that build the main list screens and run on every app open.

Adding the index in Room

Room exposes SQLite’s CREATE INDEX through the @Entity annotation’s indices parameter:

@Entity(
    tableName = "transactions",
    indices = [Index(value = ["category_id"]), Index(value = ["date"])],
)
data class Transaction(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val categoryId: Long,
    val date: Long,
    val amountCents: Long,
)

This is a schema change, so it needs a Migration, same as adding a column:

val MIGRATION_5_6 = object : Migration(5, 6) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_transactions_category ON transactions(category_id)")
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_transactions_date ON transactions(date)")
    }
}

Room’s schema export (exportSchema = true plus the room.schemaLocation compiler arg) will flag a mismatch between your @Entity indices and a hand-written migration if they drift, which is the usual way this bug gets caught before it ships.

The compound-index trap

A query that filters on category_id and sorts by date — the exact pattern above — doesn’t get full benefit from two separate single-column indices. SQLite can use one index per table per query in most cases, so it picks the more selective one and still has to sort the results in memory. A compound index covering both columns, in the right order, lets SQLite use the index for the filter and return rows already in sort order:

indices = [Index(value = ["category_id", "date"])]

Order matters here. This index serves WHERE category_id = ? and WHERE category_id = ? ORDER BY date, because both conditions read the index left-to-right. It does not help a query that filters on date alone — for that you’d still want the single-column index on date, or a second compound index with date first. Check EXPLAIN QUERY PLAN again after adding a compound index; if you still see USE TEMP B-TREE FOR ORDER BY in the plan, the column order doesn’t match what the query needs.

Indices aren’t free

Every index SQLite maintains has to be updated on every INSERT, UPDATE, or DELETE that touches an indexed column. For a table like transactions in a budgeting app, writes are relatively rare compared to reads — a handful of inserts a day against dozens of list renders — so the trade is easy. For a table that’s written constantly and read rarely (an event log, a sync queue), the same three indices that helped the transactions table can make writes measurably slower for no read benefit anyone will notice. Index the tables that get queried on every screen open, not every table in the schema.

The primary key already gets an implicit index — including it again in indices is redundant. Same for a column marked @PrimaryKey or one you’ve already declared unique = true on another index; SQLite creates the backing index automatically.

Where this actually matters

I found this the boring way, on Granyn: a transaction list that felt instant at a few hundred rows started taking a visible beat to filter by category once real usage pushed past a few thousand. EXPLAIN QUERY PLAN on the DAO query showed a full SCAN — the category_id filter had no index to use. Adding the compound index above took the filtered query from a full table scan back to an index lookup, and the stutter was gone. No architecture change, no new library, just the right two lines in a migration.

The lesson generalizes past this one table: before reaching for pagination, caching, or a rewrite when a local-first app slows down, run EXPLAIN QUERY PLAN on the query that’s actually slow. Most of the time the fix is an index, it’s small, and it’s reversible if you get it wrong.