A guide to MySQL: From Query to Production

How to work through this course

Twelve modules in four phases, and roughly eighty to a hundred hours if you do the work rather than read it. SQL is unusually easy to read and unusually hard to be good at, and the gap between the two is entirely made of practice.

Type every query. Run every query. Then break it.

SQL punishes reading-without-running more than most languages, because a query that is subtly wrong still returns rows. It does not throw; it just answers a slightly different question. The only way to develop a sense for that is to run things and look at the output - and then change one thing and look again. Remove the GROUP BY. Turn the LEFT JOIN into an INNER JOIN. Put a NULL in the column. Each of those takes ten seconds and teaches something that reading cannot.

Build one schema and keep it.

The course uses a small shop - customers, orders, order items, products. Create it in Module 2 and keep extending it: constraints in Module 5, views and triggers in Module 7, indexes in Module 9. By Module 11 you will have a database with enough in it to back up and restore for real. One schema you know well beats a dozen toy examples, because only the one you keep develops history - a column you chose badly in week two that is still annoying you in week eight is the most valuable teacher in the course.

Get enough rows to be honest.

Everything is fast with a hundred rows. Indexing, EXPLAIN and query optimisation only mean anything at scale, so before Module 9, generate a few hundred thousand orders - a recursive CTE or a short loop will do it in a minute. A full table scan across 500,000 rows is visibly slow, and watching it become instant when you add the right index is the moment indexing stops being abstract.

Pacing

Phase 1, three weeks. The foundation. Spend extra time on NULL and on WHERE, because almost every wrong-but-plausible query later traces back to one of them.

Phase 2, four weeks. The real turn is in Module 6. Joins are where people either develop a mental model or start copying queries they do not understand - and if a join is confusing, draw the two tables on paper with four rows each and work out by hand which rows should come back.

Phase 3, four to five weeks. The most valuable phase and the one worth going slowly through. Module 9 in particular: run EXPLAIN on every query you write for a week, including the ones that are already fast, until reading a plan is automatic rather than something you do when there is a problem.

Phase 4, three weeks. Administration. Less conceptual, more procedural, and the phase that turns a developer who writes SQL into someone who can be trusted with a database other people depend on.

Three habits worth forming now

Write the SELECT before the UPDATE. Every destructive statement starts life as a SELECT you looked at. This single habit prevents the most common production incident there is.

Run EXPLAIN before adding an index. Guessing at indexes produces tables with fifteen of them, none of which the slow query uses. The plan tells you what is actually happening.

Restore your backup. A backup that has never been restored is a file, not a backup. Restore it onto a scratch database and count the rows. The usual discovery is that the routines were never included.

Use the command line as well as Workbench.

Workbench is more comfortable and you should use it. But learn mysql in the terminal too, because on the day a production server is unwell the only access you will have is SSH, and the person who has never used the client is the person who cannot help.

A note on where to look things up.

The official MySQL reference manual is dense, complete and correct, and the pages for the statements in this course are worth reading in full once you have used them - it is the rare documentation that rewards a second visit. Be careful with older answers: MySQL 8 changed defaults that a great deal of advice on the internet still assumes, including ONLY_FULL_GROUP_BY, the authentication plugin, and whether CHECK constraints do anything at all.

mysql mysql queries structured query language