SQL Query Optimization Cheatsheet

-- 1. Use specific columns instead of SELECT *
SELECT id, name, email FROM users WHERE active = 1;

-- 2. Use EXISTS instead of IN for subqueries
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active = 1);

-- 3. Pagination with cursor (faster than OFFSET for large tables)
SELECT * FROM posts
WHERE id > :last_id
ORDER BY id ASC
LIMIT 20;

-- 4. Composite index usage (order matters!)
CREATE INDEX idx_posts_user_date ON posts (user_id, created_at DESC);

-- 5. Avoid N+1: use JOIN instead of loop queries
SELECT p.*, u.name as author_name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.published = 1
ORDER BY p.created_at DESC
LIMIT 10;

-- 6. Count with condition (no subquery needed)
SELECT
  COUNT(*) as total,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive_count
FROM subscriptions;

-- 7. Upsert (INSERT or UPDATE)
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;