MySQL for Application Developers
Schema design, indexing and the queries behind a real product
Most performance problems are schema problems wearing a disguise. A query that cannot be served by an index is slow no matter how it is written, and no amount of caching in front of it fixes the cause.
Overview
It also covers the point at which you do not need another piece of infrastructure. Full-text search in MySQL replaces the separate search service a surprising number of applications add before they have measured anything - and knowing where the real limits are is what makes the decision to add one defensible.
Introduction
It assumes you can already write SQL and have never been entirely sure why one query is fast and the next one is not. The answer is almost always visible in EXPLAIN, and the second module is largely about learning to read it - which is the difference between adding an index and adding the right one.
Two modules and five items, built around a single schema that grows as the course goes on. Every statement is one you can run: the tables, the constraints that keep the data honest, the query plans before and after, and a full-text search that replaces a service most teams add too early.
By the end you will be able to turn a product brief into tables that will not need rewriting, say what a composite index can and cannot serve, read a query plan without guessing, and know when MySQL is enough and when it genuinely is not.
What you will be able to do
- Model a domain in third normal form and know when to break it
- Read an EXPLAIN plan without flinching
- Use FULLTEXT indexes for search
- Write migrations that are safe to run on a live database
What each level covers
2 levels · 5 lessons
-
Modelling the Domain
Schema design, which is where performance is decided long before an index is added. The first lesson turns a product brief into tables - what is an entity, what is an attribute, and what is a join table pretending to be neither. The second is the constraints, keys and types that make invalid data impossible to store rather than merely unlikely.
2 lessons 23 min of reading
Start with Keys, Constraints and Honest Data → -
Indexes and Query Plans
Why a query is slow, answered with evidence. Reading EXPLAIN output comes first, because it is what turns index choice from folklore into a decision; then full-text search with FULLTEXT indexes, which replaces the separate search service a surprising number of applications add before they need it. The module ends with a quiz on indexing.
3 lessons 37 min of reading
Start with Reading EXPLAIN Output →
Guides and articles
Longer reading about this subject, written alongside the course.
Before you start
- Basic SQL: SELECT, JOIN, GROUP BY
Common questions
- What does the MySQL for Application Developers course cover?
- It is 2 levels and 5 lessons: Modelling the Domain; Indexes and Query Plans. Each level ends with a quiz, and the last one is an exam over the whole course.
- How long does it take?
- About 60 minutes of reading and exercises - roughly 1 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?
- Basic SQL: SELECT, JOIN, GROUP BY.
- 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 domain in third normal form and know when to break it. Read an EXPLAIN plan without flinching. Use FULLTEXT indexes for search. Write migrations that are safe to run on a live database.