Database performance

Data and storage Senior Kotlin 2.0+ API 24+ Reviewed By Best Latest Apps

Pick the fix

SELECT * FROM messages WHERE chatId = ? ORDER BY sentAt DESC LIMIT 50 takes 300 ms with 500,000 rows. Best first fix?

Options
Hint 1 of 1

What does the WHERE and ORDER BY need?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Add a composite index on (chatId, sentAt)

Correct. SQLite can find the chat's rows and read them already sorted.

Why not the others?
  • B Add separate indexes on chatId and sentAt. SQLite usually uses one index per table here; the composite fits the query.
  • C Move the query to Dispatchers.IO. It's already off main if it's a suspend DAO call; it's still slow.
  • D Cache all messages in memory. Uses lots of memory and still slow to build.

30-second answer

Look at the query plan with EXPLAIN QUERY PLAN (Database Inspector in Android Studio can run it) to see whether SQLite scans the whole table. Add indexes on columns used in WHERE, JOIN and ORDER BY. Select only the columns you need, page large results, batch writes in transactions, and keep the database work off the main thread.

Practise saying it · 35 s. No recording, just a timer

Explain how to fix a slow query.

Say your answer out loud, then check which of these points you covered:

  • EXPLAIN QUERY PLAN
  • Indexes matching WHERE and ORDER BY
  • Select fewer columns, page
  • Batch writes
  • FTS for text search

Deep dive

Indexes speed up reads but slow writes and use space; add them for real query patterns.

A composite index's column order must match how the query filters and sorts.

LIKE '%term%' can't use a normal index; consider FTS (@Fts4) for text search.

WAL mode, which Room's default AUTOMATIC journal mode picks on most devices (not low-RAM ones), lets reads run while a write is in progress.

A composite index Compile-checked · Kotlin 2.4.21
@Entity(indices = [Index(value = ["chatId", "sentAt"])])
data class Message(@PrimaryKey val id: Long, val chatId: Long, val sentAt: Long, val body: String)

Common mistakes

  • Indexing every column.
  • Loading entire rows with large blobs into lists.
  • Running thousands of inserts outside a transaction.

Senior take & follow-ups

Lead with measurement (query plan), then the fixes.

Follow-up questions

Think of your answer first, then open the hint.

  1. How do you search text fast?

    Show hint

    An FTS table with MATCH queries.

  2. Why batch writes?

    Show hint

    Each transaction syncs to disk; one big transaction is far faster.

Related questions

Suggest a fix if something here is wrong or out of date.