plugins/aai-stack-sqlite/skills/sqlite-optimization/SKILL.md
February 1, 2026
Select agents to install to:
npx add-skill https://github.com/the-answerai/alphaagent-team/blob/main/plugins/aai-stack-sqlite/skills/sqlite-optimization/SKILL.md -a claude-code --skill sqlite-optimizationInstallation paths:
.claude/skills/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