Indexes & EXPLAIN

-- Single and composite indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (smaller, targeted)
CREATE INDEX idx_posts_published ON posts(published)
WHERE published = 1;

-- Inspect the query plan
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 5 AND published = 1;

-- List indexes
SELECT name, sql FROM sqlite_master WHERE type = 'index';

-- Drop
DROP INDEX idx_users_email;

-- Analyze for stats (helps planner)
ANALYZE;