Database Index Optimizer
Overview
Advanced index optimization and query performance analysis for GRDB.swift applications under production load conditions.
Core principle Systematic index design and query plan optimization for production-scale SQLite databases.
Requires iOS 13+, Swift 5.7+, GRDB 6.0+ License MIT (free and open source)
When to Use This Skill
Use this skill when you need
- ✅ Optimize slow queries (>500ms response time)
- ✅ Design composite indexes for multi-column queries
- ✅ Analyze EXPLAIN QUERY PLAN output
- ✅ Reduce database size through index consolidation
- ✅ Profile production query performance at scale
Use basic GRDB patterns when
- Simple single-table queries
- Databases under 10,000 records
- Read-only operations without joins
Index Design Fundamentals
Single-Column Indexes
try db.create(index: "idx_users_email", on: "users", columns: ["email"])
try db.create(index: "idx_posts_created_at", on: "posts", columns: ["created_at"])
When to use Frequently filtered or sorted columns.
Composite Indexes (Multi-Column)
// Index for WHERE status = ? AND priority = ? ORDER BY created_at
try db.create(index: "idx_tasks_status_priority_created",
on: "tasks",
columns: ["status", "priority", "created_at"])
Column ordering matters Place equality filters before range filters before sort columns.
Covering Indexes
// Index that contains all queried columns
try db.create(index: "idx_users_email_name",
on: "users",
columns: ["email", "name"])
// Query can be satisfied entirely from index
let result = try db.fetchAll(sql: "SELECT email, name FROM users WHERE email = ?",
arguments: [email])
Query Plan Analysis
Understanding EXPLAIN QUERY PLAN
let plan = try db.execute(sql: """
EXPLAIN QUERY PLAN
SELECT * FROM posts
WHERE user_id = ? AND status = 'published'
ORDER BY created_at DESC
""", arguments: [userId])
// Look for:
// SEARCH ... USING INDEX (good - index used)
// SCAN TABLE (bad - full table scan)
// USE TEMP B-TREE FOR ORDER BY (suboptimal - no sort index)
Index Selection Rules
SQLite chooses indexes based on:
- Selectivity - Fewer matching rows = preferred index
- Column order - Left-prefix matching required
- Coverage - Covering indexes avoid table lookups
Production Performance Patterns
Composite Index Performance
Composite indexes provide significant performance improvements over single-column alternatives:
// Before: Single-column index
try db.create(index: "idx_orders_user", on: "orders", columns: ["user_id"])
try db.create(index: "idx_orders_status", on: "orders", columns: ["status"])
// After: Composite index
try db.create(index: "idx_orders_user_status_created",
on: "orders",
columns: ["user_id", "status", "created_at"])
Performance impact Composite indexes typically reduce query execution time and improve index utilization in multi-condition queries.
Covering Index Optimization
Covering indexes eliminate the need for table lookups by including all queried columns:
// Query requires: user_id, email, name
try db.create(index: "idx_users_email_covering",
on: "users",
columns: ["email", "user_id", "name"])
let users = try db.fetchAll(sql: """
SELECT user_id, email, name
FROM users
WHERE email LIKE ?
""", arguments: ["%@example.com"])
Optimization benefit Covering indexes can substantially reduce disk I/O by serving queries directly from index data.
Partial Indexes
Partial indexes with WHERE clauses optimize specific query patterns while reducing index maintenance overhead:
// Only index active users
try db.execute(sql: """
CREATE INDEX idx_users_active_email
ON users(email)
WHERE status = 'active'
""")
// Only index recent posts
try db.execute(sql: """
CREATE INDEX idx_posts_recent_created
ON posts(created_at)
WHERE created_at > date('now', '-30 days')
""")
Write performance Partial indexes improve insertion and update performance for filtered datasets.
Index Maintenance Strategies
Index Consolidation
// Identify redundant indexes
let indexInfo = try db.execute(sql: "PRAGMA index_list('users')")
// Consolidate: If you have idx_users_email AND idx_users_email_name,
// the single-column index is redundant (left-prefix rule)
try db.execute(sql: "DROP INDEX IF EXISTS idx_users_email")
Periodic Analysis
// Update SQLite statistics for better query planning
try db.execute(sql: "ANALYZE")
// Check index usage
try db.execute(sql: "PRAGMA index_info('idx_users_email_name')")
Index Size Monitoring
let indexSize = try db.fetchOne(Int.self, sql: """
SELECT SUM(pgsize)
FROM dbstat
WHERE name LIKE 'idx_%'
""")
print("Total index size: \(indexSize ?? 0) bytes")
Advanced Query Optimization
Multi-Table Join Optimization
// Ensure indexes exist on JOIN columns
try db.create(index: "idx_posts_user_id", on: "posts", columns: ["user_id"])
try db.create(index: "idx_comments_post_id", on: "comments", columns: ["post_id"])
let results = try db.fetchAll(sql: """
SELECT users.name, posts.title, COUNT(comments.id) as comment_count
FROM users
JOIN posts ON posts.user_id = users.id
LEFT JOIN comments ON comments.post_id = posts.id
WHERE users.status = 'active'
GROUP BY posts.id
ORDER BY comment_count DESC
""")
Subquery Optimization
// Materialize expensive subqueries with indexes
try db.execute(sql: """
CREATE TEMP TABLE active_user_ids AS
SELECT id FROM users WHERE status = 'active'
""")
try db.create(index: "idx_temp_active_ids", on: "active_user_ids", columns: ["id"])
let posts = try db.fetchAll(sql: """
SELECT * FROM posts
WHERE user_id IN (SELECT id FROM active_user_ids)
""")
Performance Profiling Tools
Query Execution Timing
import Foundation
func profileQuery<T>(_ query: () throws -> T) rethrows -> (result: T, duration: TimeInterval) {
let start = Date()
let result = try query()
let duration = Date().timeIntervalSince(start)
return (result, duration)
}
let (users, duration) = profileQuery {
try db.fetchAll(sql: "SELECT * FROM users WHERE email LIKE ?", arguments: ["%@example.com"])
}
print("Query executed in \(duration * 1000)ms")
Trace Logging
var dbQueue = try DatabaseQueue(path: dbPath)
dbQueue.trace { event in
switch event {
case .statement(let statement):
print("SQL: \(statement.sql)")
print("Duration: \(statement.duration)s")
default:
break
}
}
Migration to Optimized Indexes
Safe Index Replacement
var migrator = DatabaseMigrator()
migrator.registerMigration("v3_optimize_user_indexes") { db in
// Create new composite index
try db.create(index: "idx_users_status_email_name_new",
on: "users",
columns: ["status", "email", "name"])
// Remove old single-column indexes
try db.execute(sql: "DROP INDEX IF EXISTS idx_users_status")
try db.execute(sql: "DROP INDEX IF EXISTS idx_users_email")
// Update statistics
try db.execute(sql: "ANALYZE users")
}
try migrator.migrate(dbQueue)
Best Practices Checklist
- ✅ Create indexes on foreign key columns used in JOINs
- ✅ Use composite indexes for multi-column WHERE clauses
- ✅ Design covering indexes for frequently-run SELECT queries
- ✅ Apply partial indexes for filtered datasets (e.g., active records only)
- ✅ Run EXPLAIN QUERY PLAN on slow queries (>500ms)
- ✅ Execute ANALYZE after major data imports
- ✅ Monitor index size vs. table size ratio
- ✅ Remove redundant indexes (left-prefix rule)
- ✅ Profile query execution time in production
- ✅ Consider index maintenance cost for high-write tables
Common Index Anti-Patterns
❌ Over-indexing Write-Heavy Tables
// Don't create 10 indexes on a table with frequent inserts
// Each index slows down writes
Fix Prioritize indexes for actual query patterns, not theoretical use cases.
❌ Missing Composite Indexes
// Separate indexes don't combine well
try db.create(index: "idx_status", on: "tasks", columns: ["status"])
try db.create(index: "idx_priority", on: "tasks", columns: ["priority"])
// SQLite will only use one index for:
// WHERE status = ? AND priority = ?
Fix Create a composite index covering both columns.
❌ Wrong Column Order in Composite Index
// Bad: Range filter first
try db.create(index: "idx_tasks_created_status",
on: "tasks",
columns: ["created_at", "status"])
// Query: WHERE status = ? AND created_at > ?
// Only uses the index partially
Fix Place equality filters before range filters.
Resources
GRDB: github.com/groue/GRDB.swift
SQLite Docs: sqlite.org/queryplanner.html
Index Design: sqlite.org/optoverview.html
Quick Reference
// Create single-column index
try db.create(index: "idx_table_column", on: "table", columns: ["column"])
// Create composite index
try db.create(index: "idx_table_col1_col2", on: "table", columns: ["col1", "col2"])
// Create unique index
try db.create(index: "idx_table_unique", on: "table", columns: ["column"], unique: true)
// Create partial index
try db.execute(sql: "CREATE INDEX idx_name ON table(column) WHERE condition")
// Analyze query plan
try db.execute(sql: "EXPLAIN QUERY PLAN SELECT ...")
// Update statistics
try db.execute(sql: "ANALYZE")
// Drop index
try db.execute(sql: "DROP INDEX IF EXISTS idx_name")
Targets: iOS 13+, Swift 5.7+
Framework: GRDB.swift 6.0+
Focus: Production-scale query optimization