Maxing out SQLite performance
summary
Shows concrete index strategies for SQLite: single, composite, partial, expression and covering indexes; B‑tree storage and use of EXPLAIN QUERY PLAN. On a 1M‑row test a sender lookup drops from 53 ms to 1 ms with an index. Notes Room supports single/multi‑column indexes but partial and expression indexes not supported in Room so migrations must run raw SQL. Highlights reactive re‑emit bugs from the invalidation tracker and the fix using distinctUntilChanged. Also warns that SQLite views are read‑only and not materialized.
AI-written summary · about
chapters (10)
- 0:00Start
- 6:48Sample problem
- 9:39Single column index - continued
- 12:41Partial Index
- 15:31Expression-based Index
- 17:01Covering Index
- 19:09Room (support and compatibility)
- 21:19Potential issue in the reactive world!
- 24:56Database Triggers & Room - continued
- 28:45Concerns around Database Views in SQLite
related talks
45mSQLite. Incredibly Fast
46mSupercharge Your SQL with Indexes: a Practical Introduction
49mIt’s Not Always About the Index: The Hidden Secrets of Query Performance
44mInside the Room
36mFor performance, go to page 5: Indexes
45mInside the Room
45mSQL Indexing Beyond Simple Columns: Expressions, Text Fragments, Attributes J...
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries