Notes on writing SQL by hand

Where slow queries come from

Most performance issues are not "slow SQL" but missing indexes or fetching more than you need.

-- Select only what you need instead of SELECT *
SELECT id, title_en, published_at
FROM post
WHERE published = 1
ORDER BY published_at DESC
LIMIT 10;

When to add an index

1. Columns frequently used in WHERE, JOIN or ORDER BY
2. High selectivity (status flags are usually poor candidates)
3. Verify with EXPLAIN instead of guessing

Summary

Measure first, optimize second. Optimization without metrics is usually self-comfort.