SQLite Partial Indexes and Expression Indexes in Mobile Apps

-- Index only tasks awaiting processing, ordered by priority
CREATE INDEX idx_sync_pending_priority 
ON sync_tasks(priority DESC) 
WHERE status = 'PENDING';

In Room, apply this via a RoomDatabase.Callback or migration script:

@Database(entities = [SyncTask::class], version = 2)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao

    companion object {
        private val CALLBACK = object : Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                db.execSQL(
                    """
                    CREATE INDEX idx_sync_pending_priority 
                    ON sync_tasks(priority DESC) 
                    WHERE status = 'PENDING'
                    """.trimIndent()
                )
            }
        }
    }
}

Architecture Rationale: Using onCreate ensures the index exists before any DAO queries execute. For existing apps, wrap this in a migration block to avoid schema version conflicts. The WHERE clause is evaluated at index build time, not query time, guaranteeing zero overhead for non-matching rows.

Step 3: Add Expression Indexes for Temporal Filtering

Mobile apps frequently query by calendar date while storing timestamps as epoch milliseconds. Expression indexes precompute the transformation, allowing the planner to match queries directly.

-- Precompute date string from epoch millis
CREATE INDEX idx_sync_task_date 
ON sync_tasks(strftime('%Y-%m-%d', createdAt / 1000, 'unixepoch'));

Query matching the expression:

@Query("""
    SELECT * FROM sync_tasks 
    WHERE strftime('%Y-%m-%d', createdAt / 1000, 'unixepoch') = :targetDate
    ORDER BY priority DESC
""")
fun getTasksForDate(targetDate: String): List<SyncTask>

Why this works: SQLite's query planner performs exact string matching on index expressions. If the query expression matches the index definition character-for-character, the planner bypasses runtime computation and performs a direct B-tree seek.

Step 4: Construct Covering Indexes for Paginated Feeds

Cursor-based pagination requires filtering, sorting, and returning payload columns. A covering index includes all requested columns, eliminating the need for a secondary table lookup (rowid fetch).

-- Covers filter, sort, and payload columns
CREATE INDEX idx_sync_feed_cover 
ON sync_tasks(createdAt DESC, taskId, payload, status) 
WHERE status != 'COMPLETED';

DAO implementation:

@Query("""
    SELECT taskId, payload, status FROM sync_tasks 
    WHERE status != 'COMPLETED' 
    ORDER BY createdAt DESC 
    LIMIT :limit OFFSET :offset
""")
fun getFeedPage(limit: Int, offset: Int): List<SyncTask>

Column Order Rationale:

1. Equality/Filter columns first (status in WHERE)
2. Sort columns second (createdAt DESC)
3. Payload columns last (taskId, payload, status) This ordering allows the planner to perform a single index seek, satisfy the ORDER BY without a filesort, and return data directly from the index leaf nodes.

Step 5: Verify Execution Plans

Never assume the planner uses your index. Inject verification logic into debug builds:

object QueryPlanVerifier {
    fun logPlan(db: SupportSQLiteDatabase, query: String) {
        if (BuildConfig.DEBUG) {
            db.query("EXPLAIN QUERY PLAN $query").use { cursor ->
                while (cursor.moveToNext()) {
                    Log.d("QP", cursor.getString(3))
                }
            }
        }
    }
}

Expected output for a working partial covering index: SEARCH TABLE sync_tasks USING COVERING INDEX idx_sync_feed_cover

If you see SCAN TABLE or SEARCH TABLE without USING INDEX, the planner rejected the index. Proceed to the Pitfall Guide.

Pitfall Guide

1. Parameterized Predicates Defeat Partial Indexes

Explanation: SQLite's query planner cannot prove at plan time that a bound parameter (:status) will always equal the static value in the WHERE clause. It conservatively abandons the partial index. Fix: Use literal values in DAO queries. If dynamic filtering is required, split the logic into separate DAO methods with hardcoded predicates, or use @RawQuery with explicit SQL construction.

2. Expression Signature Mismatch

Explanation: The planner matches index expressions using exact string comparison. Whitespace differences, function aliasing, or implicit type casting will cause silent index abandonment. Fix: Extract the expression to a const val and reuse it in both the migration script and the @Query annotation. Verify with EXPLAIN QUERY PLAN after every schema change.

3. Covering Index Column Misordering

Explanation: Placing sort columns before filter columns forces a full index scan instead of a targeted seek. The planner cannot skip irrelevant branches if the leading column lacks selectivity. Fix: Always order columns as: WHERE equality/filter → ORDER BY → SELECT payload. Test with EXPLAIN QUERY PLAN to confirm SEARCH vs SCAN.

4. Silent Index Abandonment on ORM Updates

Explanation: Room generates SQL based on entity definitions. If you modify a query to use IN, LIKE, or complex joins, the planner may fall back to table scans even if a suitable index exists. Fix: Treat EXPLAIN QUERY PLAN as a CI gate. Log execution plans for all DAO methods in debug builds and fail builds if SCAN appears on tables exceeding 10K rows.

5. Over-Indexing Low-Cardinality Columns

Explanation: Creating full indexes on boolean or enum columns with high cardinality skew wastes storage and increases write latency. The planner often ignores them anyway due to low selectivity. Fix: Replace full indexes with partial indexes targeting the active state. Only index columns that appear in WHERE, JOIN, or ORDER BY clauses.

6. Migration State Drift

Explanation: Applying indexes via onCreate but forgetting to add them to migration scripts causes schema mismatches on upgraded installations. The app may crash or silently degrade performance. Fix: Maintain idempotent migration blocks. Use CREATE INDEX IF NOT EXISTS and version your database schema explicitly. Audit migration scripts during every release cycle.

7. Ignoring Cache Warming Effects

Explanation: Benchmarks on empty or small datasets show negligible differences between indexed and unindexed queries. Real-world performance gains only appear when the page cache is saturated and I/O becomes the bottleneck. Fix: Populate test databases with production-scale data (500K+ rows). Measure cold vs warm query latency. Validate index effectiveness under memory pressure by limiting SQLite's cache size during testing.

Production Bundle

Action Checklist

• Audit schema for low-cardinality columns used in WHERE clauses
• Identify queries with consistent filter predicates (e.g., status = 'PENDING')
• Draft partial indexes targeting only the active state
• Verify expression indexes use exact string matches in DAO queries
• Order covering index columns: filter → sort → payload
• Inject EXPLAIN QUERY PLAN logging into debug builds
• Populate test database with 500K+ rows for realistic benchmarking
• Add migration scripts for all new indexes with version bump

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-volume status filter (e.g., pending syncs)	Partial index on status + sort column	Eliminates 90%+ of index pages, reduces write amplification	Storage: ↓98%, Write latency: ↓3x
Date-range reporting on epoch timestamps	Expression index with strftime conversion	Precomputes transformation, enables direct B-tree seek	CPU: ↓40%, Query time: ↓5x
Cursor-based pagination with payload return	Partial covering index	Removes table lookups, satisfies ORDER BY natively	I/O pages: ↓99%, Scroll latency: ↓10x
Write-heavy sync queue with frequent updates	Avoid full indexes; use partial + covering	Prevents B-tree fragmentation and page cache eviction	Write throughput: ↑2x, Battery: ↑15%
Ad-hoc analytics on low-cardinality flags	Full index or materialized view	Partial indexes cannot serve dynamic predicates efficiently	Storage: ↑, Read flexibility: ↑

Configuration Template

@Database(entities = [SyncTask::class], version = 3)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao

    companion object {
        private val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    """
                    CREATE INDEX IF NOT EXISTS idx_sync_pending_priority 
                    ON sync_tasks(priority DESC) 
                    WHERE status = 'PENDING'
                    """.trimIndent()
                )
                database.execSQL(
                    """
                    CREATE INDEX IF NOT EXISTS idx_sync_task_date 
                    ON sync_tasks(strftime('%Y-%m-%d', createdAt / 1000, 'unixepoch'))
                    """.trimIndent()
                )
                database.execSQL(
                    """
                    CREATE INDEX IF NOT EXISTS idx_sync_feed_cover 
                    ON sync_tasks(createdAt DESC, taskId, payload, status) 
                    WHERE status != 'COMPLETED'
                    """.trimIndent()
                )
            }
        }

        fun buildInstance(context: Context): AppDatabase {
            return Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "app_database"
            )
                .addMigrations(MIGRATION_2_3)
                .addCallback(object : Callback() {
                    override fun onCreate(db: SupportSQLiteDatabase) {
                        super.onCreate(db)
                        // Fallback for fresh installs
                        db.execSQL("CREATE INDEX IF NOT EXISTS idx_sync_pending_priority ON sync_tasks(priority DESC) WHERE status = 'PENDING'")
                        db.execSQL("CREATE INDEX IF NOT EXISTS idx_sync_task_date ON sync_tasks(strftime('%Y-%m-%d', createdAt / 1000, 'unixepoch'))")
                        db.execSQL("CREATE INDEX IF NOT EXISTS idx_sync_feed_cover ON sync_tasks(createdAt DESC, taskId, payload, status) WHERE status != 'COMPLETED'")
                    }
                })
                .build()
        }
    }
}

@Dao
interface TaskDao {
    @Query("SELECT * FROM sync_tasks WHERE status = 'PENDING' ORDER BY priority DESC")
    fun getPendingTasks(): List<SyncTask>

    @Query("SELECT * FROM sync_tasks WHERE strftime('%Y-%m-%d', createdAt / 1000, 'unixepoch') = :date")
    fun getTasksByDate(date: String): List<SyncTask>

    @Query("SELECT taskId, payload, status FROM sync_tasks WHERE status != 'COMPLETED' ORDER BY createdAt DESC LIMIT :limit OFFSET :offset")
    fun getFeedPage(limit: Int, offset: Int): List<SyncTask>
}

Quick Start Guide

1. Identify Target Queries: Run EXPLAIN QUERY PLAN on your top 5 DAO queries. Note any SCAN TABLE outputs on tables with >50K rows.
2. Draft Partial Indexes: For each query with a static filter predicate, write a CREATE INDEX ... WHERE ... statement targeting only the active state.
3. Apply via Migration: Add the index definitions to a new Migration block and increment the database version. Include fallback onCreate logic for fresh installs.
4. Verify Execution Plans: Re-run EXPLAIN QUERY PLAN on the same queries. Confirm SEARCH TABLE ... USING INDEX or COVERING INDEX appears.
5. Benchmark Under Load: Populate a test database with production-scale data. Measure cold query latency and I/O page reads. Iterate column ordering if the planner falls back to scans.