How do you diagnose and optimize a slow Room query?

What's really being asked

This question probes whether you treat database performance as a measurement problem rather than a guessing game. The interviewer wants to see that you understand the boundary between Room and SQLite, know how to use Android profiling tools, and can apply relational database optimization techniques specifically in a mobile context where I/O and memory are constrained.

The full answer

First, diagnosis. Mention using Android Studio Database Inspector to view running queries and their execution times, or adding custom logging via Room's SupportSQLiteOpenHelperFactory to capture slow queries in production. Second, query plan analysis. Explain that you would run EXPLAIN QUERY PLAN on the generated SQL to identify full-table scans, temporary B-tree sorts, or missing index usage. Third, concrete Room or SQLite optimizations. One, add a covering index or composite index if the query filters or joins on unindexed columns. Two, rewrite the query to avoid nested subqueries or N plus one patterns by flattening joins or using Room relations carefully. Three, reduce the working set with Paging3 or a LIMIT clause so Room does not inflate enormous object graphs into memory. Four, mention enabling WAL mode via RoomDatabase.Builder if read concurrency is the bottleneck, since WAL allows readers to proceed without blocking writers.

The mistakes people make

A major red flag is suggesting to abandon Room or SQLite for another database like Realm or Firestore without profiling first. Another is proposing to cache everything in memory to avoid the database, which ignores memory pressure and lifecycle issues. Blindly adding indexes on every column is also wrong because each index slows down writes and increases storage. Finally, blaming Room overhead itself is weak; Room is a thin abstraction over SQLite, so the issue is almost always the query or schema.

What usually comes next

The interviewer may ask how you would detect this in production rather than locally, so be ready to discuss Firebase Performance Monitoring or custom traces around DAO methods. They might also ask how Room's PagingSource interacts with SQLite cursors under the hood, or how migrations handle new indexes without locking the database for large tables. Another follow-up is how you would optimize a query that must remain complex, such as one with multiple many-to-many joins.

A concrete example

Suppose a Room query loads a user's feed by joining Posts, Authors, and Likes tables and filtering by timestamp. The DAO returns a large list without pagination. In Android Studio you notice the query takes 300 ms on a low-end device. EXPLAIN QUERY PLAN shows a SCAN TABLE Likes because the foreign key is not indexed. You add an index on Likes(post_id) and rewrite the DAO to return a PagingSource with a page size of 20. The plan switches to SEARCH TABLE Likes USING INDEX, and the UI only materializes rows as needed, dropping jank and memory pressure.