Mobile DB Optimization: Indexes, N+1, Background Contexts
Why N+1 Queries Create Performance Bottlenecks?
We often encounter situations where loading a list of orders is followed by a separate query for each user. 100 orders = 101 SQLite queries. This is the classic N+1 query problem. With 1000 records, that's 1001 queries, each possibly taking 5–10 ms, totaling 5–10 seconds of UI blocking. CoreData solves this via relationshipKeyPathsForPrefetching, Room via @Relation with @Transaction, Flutter + sqflite via a JOIN query instead of nested loops. A typical mistake: developers don't monitor the number of queries in the log. The result: the app lags, and the issue lies in a few lines of code.
How Indexes Accelerate Queries by 10x
SQLite underlies CoreData, Room, and most mobile ORMs. A WHERE on a non-indexed field over 50,000 rows performs a full scan. On Android Room, add @Index to the entity; on iOS CoreData, set indexed in the Data Model Inspector. Without a B-tree index, a query might take 500 ms; with an index, 5 ms. A typical example: searching for a product by name on a 200,000-row table without an index takes 1.8 seconds; with an index, 40 ms. Always add indexes on fields involved in WHERE, JOIN, and ORDER BY.
Platform-Specific Solutions
iOS — CoreData
NSPersistentContainer provides newBackgroundContext() for background operations. The correct pattern uses background contexts:
container.performBackgroundTask { context in // bulk operations here try? context.save() DispatchQueue.main.async { // UI update } } NSFetchRequest.fetchBatchSize = 20 — CoreData loads data in batches as accessed, not all at once. NSFetchedResultsController with sectionNameKeyPath for sectioned tables is the correct pattern that automatically updates UITableView when data changes.
For bulk inserts, NSBatchInsertRequest (iOS 13+) writes directly to SQLite without creating managed objects — 10–20 times faster than standard insert for thousands of records.
Android — Room
Use @Query with EXPLAIN QUERY PLAN via adb shell to quickly check for full scans. Room @TypeConverter for JSON fields via Gson/Moshi works, but slows down bulk fetches — normalize your data instead.
Flow<List<Entity>> from Room automatically emits new data when the table changes — no need to manually invalidate cache. distinctUntilChanged() prevents unnecessary emissions if data hasn't changed.
Room.databaseBuilder().setQueryCoroutineContext(Dispatchers.IO) explicitly directs Room queries to the IO dispatcher.
Flutter — Drift
Drift (formerly Moor) is the preferred choice for complex schemas: type-safe queries, migrations, code generation. Use database.transaction() for batch operations — within a transaction, 1000 INSERTs execute in 50–100 ms; without a transaction, it takes 5–10 seconds (each INSERT opens/closes a SQLite transaction).
How FTS Helps Search Through 200,000 Records
From our practice: an offline product catalog app — searching by name on Room without FTS took 1.8 seconds. We integrated FTS4:
@Fts4 @Entity(tableName = "products_fts") data class ProductFts(val name: String, val description: String) A MATCH query on the FTS table took 40–60 ms on the same dataset. As SQLite developers note, FTS4 delivers full-text search in milliseconds. For iOS CoreData, use NSPredicate with MATCH via raw SQLite if you need fast full-text search.
Step-by-Step Guide: How to Optimize a Mobile App Database
Follow these steps to optimize your mobile database.
Step 1: Schema Audit
Identify tables without indexes and problematic queries using EXPLAIN QUERY PLAN.
Step 2: Add Indexes
Create B-tree indexes on fields involved in WHERE, JOIN, and ORDER BY.
Step 3: Eliminate N+1 Queries
Use prefetching in CoreData or JOIN in Room/Drift.
Step 4: Batch Operations
Use NSBatchInsertRequest, Room's @Insert with annotation, or Drift transactions for bulk inserts.
Step 5: Performance Testing
Measure query time before and after optimization, ensure no UI blocking.
Performance Comparison Before and After Optimization
| Query | Before Optimization | After Optimization |
|---|---|---|
| Search by name (200k records) | 1.8 s (LIKE) | 40 ms (FTS4) |
| Load order list with users (N+1) | 1.1 s (101 queries) | 20 ms (1 JOIN) |
| Bulk insert 10,000 records | 15 s (one by one) | 600 ms (batch) |
Example code for batch insert in Drift
await database.transaction(() async { for (final item in items) { await database.insert(item); } }); What’s Included in Optimization
| Stage | What We Do | Duration |
|---|---|---|
| Schema Audit | Analyze tables, indexes, queries | 1-2 days |
| Optimization | Indexes, batch operations, background contexts | 3-7 days |
| Testing | Performance and regression checks | 1-2 days |
| Documentation | Monitoring recommendations | Included |
Deliverables
- Detailed documentation of all changes.
- Access to performance monitoring dashboard.
- Training for your team (1-2 hours).
- Post-optimization support for 30 days.
Cost Savings and ROI
Optimizing a typical e-commerce app can reduce query time by 97%, saving $2,000-$5,000 per month in developer time otherwise spent fixing user complaints. Typical cost savings from optimization are $2,000-$5,000 per month, while the upfront cost is $500 for an audit or $1,500-$7,000 for a full package. Our audit starts at $500, and full optimization packages range from $1,500-$7,000 depending on complexity.
Our experience: 5 years in mobile development, over 50 projects with DB optimization. We guarantee measurable performance improvements. Contact us to get a consultation and cost estimate. Request an audit today and receive a detailed report with recommendations.