sqlite-optimization

# SQLite Optimization Skill

Techniques for optimizing SQLite performance.

## PRAGMA Settings

### Performance PRAGMAs

```sql
-- Enable Write-Ahead Logging (recommended for most use cases)
PRAGMA journal_mode = WAL;

-- Synchronous mode (tradeoff: safety vs speed)
PRAGMA synchronous = NORMAL;  -- Good balance
-- PRAGMA synchronous = OFF;  -- Fastest, but risky
-- PRAGMA synchronous = FULL; -- Safest, slowest

-- Memory cache size (in KB, negative = KB)
PRAGMA cache_size = -64000;  -- 64MB

-- Memory-mapped I/O (bytes)
PRAGMA mmap_size = 268435456;  -- 256MB

-- Busy timeout
PRAGMA busy_timeout = 5000;  -- 5 seconds

-- Temp storage
PRAGMA temp_store = MEMORY;
```

### Query Analysis PRAGMAs

```sql
-- Check foreign keys
PRAGMA foreign_key_check;

-- Integrity check
PRAGMA integrity_check;

-- Quick check
PRAGMA quick_check;

-- Show table info
PRAGMA table_info(users);

-- Show index list
PRAGMA index_list(users);
```

## Query Optimization

### EXPLAIN QUERY PLAN

```sql
-- Analyze query execution plan
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com';

-- Look for:
-- "SCAN" = full table scan (often bad)
-- "SEARCH" = using index (good)
-- "USING INDEX" = covering index (best)
```

### Index Optimization

```sql
-- Covering index (includes all needed columns)
CREATE INDEX idx_users_covering
ON users(email, name, created_at);

-- Now this query uses index only, no table access:
SELECT email, name FROM users WHERE email LIKE 'test%';

-- Partial index for common queries
CREATE INDEX idx_active_users
ON users(email)
WHERE active = 1;

-- Expression index
CREATE INDEX idx_users_lower_email
ON users(lower(email));
```

### Query Patterns

```sql
-- BAD: OR conditions prevent index use
SELECT * FROM users
WHERE email = 'a@test.com' OR email = 'b@test.com';

-- GOOD: Use UNION or IN
SELECT * FROM users WHERE email IN ('a@test.com', 'b@test.com');

-- BAD: Function on indexed column
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- G