MySQL: From Query to Production
From your first SELECT to replication, backups and a query plan you can read.
MySQL runs a very large share of the software you use. It is the database behind most of WordPress, behind a great many internal tools, and behind a long tail of applications that started small and are now load-bearing. Learning it well is one of the highest-leverage things a developer can do, because the database is where the data lives and almost every performance problem that survives a code review turns out to be a query.
Overview
Every lesson is free to read in full. The examples are written against one small schema that grows with the course, so by the end you have a database you built yourself, indexed yourself, broke yourself, and fixed by reading EXPLAIN rather than by guessing.
Introduction
What you do need is somewhere to run it. MySQL 8.0 or later is free, runs on Windows, macOS and Linux, and Module 1 walks through installing it alongside MySQL Workbench. If you would rather not install anything, a Docker container is one command and the course notes it where relevant. MariaDB is close enough that most of this course applies, and the places where it diverges are called out.
The later phases describe things you should not practise on a database anyone depends on. Run them on a copy. That is not a disclaimer; it is the first habit of anybody who works with production data.
What you will be able to do
- Model a problem as tables, with the keys and constraints that keep the data honest
- Write queries that filter, sort, group and join across several tables without guessing
- Choose the right data type, and know what each one costs
- Use views, stored procedures, functions and triggers - and know when not to
- Reason about transactions, isolation and deadlocks rather than retrying blindly
- Read an EXPLAIN plan and say why a query is slow before changing anything
- Design an index that a query actually uses, and recognise one that is wasted
- Create users with the privileges they need and no more
- Take a backup, and restore it, which is not the same thing
- Set up replication and understand when partitioning helps
What each level covers
12 levels · 33 lessons
-
Module 1 - Databases and the MySQL Architecture
Phase 1. What a relational database is for, how MySQL is put together, and getting a server running in front of you.
3 lessons 6 min of reading
Start with Why a Database, and What Makes One Relational → -
Module 2 - Creating Databases and Tables
Phase 1. DDL, the data types worth knowing, and a table definition that will not embarrass you later.
3 lessons 7 min of reading
Start with DDL and Creating a Database → -
Module 3 - Querying and Changing Data
Phase 1. Getting rows in, getting the right rows out, and changing them without taking the table with you.
4 lessons 8 min of reading
Start with Inserting Rows → -
Module 4 - Functions and Aggregation
Phase 2. Summarising many rows into few, and the built-in functions that shape what comes back.
3 lessons 8 min of reading
Start with Aggregates, GROUP BY and HAVING → -
Module 5 - Relational Design and Constraints
Phase 2. Normalising a schema, and the rules that make the database refuse bad data rather than trusting your code.
3 lessons 8 min of reading
Start with Normalisation → -
Module 6 - Joins and Subqueries
Phase 2. Reading from several tables at once, and asking a question whose answer is another query.
3 lessons 9 min of reading
Start with Inner and Outer Joins → -
Module 7 - Views, Routines and Triggers
Phase 3. Putting logic in the database - and being deliberate about how much of it belongs there.
3 lessons 9 min of reading
Start with Views → -
Module 8 - Transactions, Locking and Concurrency
Phase 3. What happens when two people write at once, and the guarantees that stop it being a mess.
2 lessons 6 min of reading
Start with ACID and Transactions → -
Module 9 - Indexing and Query Optimisation
Phase 3. Why a query is slow, how to find out rather than guess, and the indexes that fix it.
3 lessons 9 min of reading
Start with Storage Engines → -
Module 10 - Users, Privileges and Security
Phase 4. Accounts with the privileges they need and no more, and a server that is not trivially reachable.
2 lessons 5 min of reading
Start with Users and Authentication → -
Module 11 - Backup, Restore and Maintenance
Phase 4. A backup you have actually restored, bulk loading, and keeping a table in shape.
2 lessons 6 min of reading
Start with Backups and Restores → -
Module 12 - Replication, Scaling and High Availability
Phase 4. More than one server, and what to do when one table is bigger than the rest put together.
2 lessons 6 min of reading
Start with Replication →
Guides and articles
Longer reading about this subject, written alongside the course.
-
An index is a second copy of your data, sorted - and everything follows from that
Most indexing advice is a list of rules to memorise. It is all one mechanism: a sorted copy of some columns with a pointer back to the row. Understand the copy and the rules stop needing to be remembered.
· 9 min read
Before you start
- MySQL Server 8.0 or later - free, on any operating system
- MySQL Workbench, or any client you prefer
- A terminal, for the command-line client and mysqldump
- About 2 GB of disk space
- No prior SQL
Common questions
- What does the MySQL: From Query to Production course cover?
- It is 12 levels and 33 lessons: Module 1 - Databases and the MySQL Architecture; Module 2 - Creating Databases and Tables; Module 3 - Querying and Changing Data; Module 4 - Functions and Aggregation; Module 5 - Relational Design and Constraints; Module 6 - Joins and Subqueries; Module 7 - Views, Routines and Triggers; Module 8 - Transactions, Locking and Concurrency; Module 9 - Indexing and Query Optimisation; Module 10 - Users, Privileges and Security; Module 11 - Backup, Restore and Maintenance; Module 12 - Replication, Scaling and High Availability. Each level ends with a quiz, and the last one is an exam over the whole course.
- How long does it take?
- About 87 minutes of reading and exercises - roughly 1.5 hours. It is self-paced with no cohort and no start date, so you can leave and come back and the site remembers where you were.
- What do I need to know first?
- MySQL Server 8.0 or later - free, on any operating system. MySQL Workbench, or any client you prefer. A terminal, for the command-line client and mysqldump. About 2 GB of disk space. No prior SQL.
- Is it free?
- Yes. Every lesson, example and quiz in this course is free to read, and you do not need an account to read them. Signing in only adds progress tracking and quiz results.
- What will I be able to do afterwards?
- Model a problem as tables, with the keys and constraints that keep the data honest. Write queries that filter, sort, group and join across several tables without guessing. Choose the right data type, and know what each one costs. Use views, stored procedures, functions and triggers - and know when not to. Reason about transactions, isolation and deadlocks rather than retrying blindly. Read an EXPLAIN plan and say why a query is slow before changing anything. Design an index that a query actually uses, and recognise one that is wasted. Create users with the privileges they need and no more. Take a backup, and restore it, which is not the same thing. Set up replication and understand when partitioning helps.