Welcome! MySQL for Developers is PlanetScale’s free MySQL course that teaches you schema basics, indexing, querying, and beyond. This intermediate MySQL course is appropriate for both developers just getting started with MySQL and those who need a refresher. The course has been created with usability and application in mind. Everything you learn in this course can be taken and applied to your own applications to make them faster and more efficent.

We’ll be breaking the course down into several sections, starting with the schema and covering everything from data types to indexes, to querying, and much more.

Section 1: Designing and efficient schema in MySQL

In the first section of the course, we’re going to start by looking at schema basics. What makes an effective table? How do different data types in MySQL work? When should you use one type over another? We’ll also look at the drawbacks of certain data types and how to avoid them while building a schema. This is the foundation for the rest of the course, so make sure you understand these concepts before moving on to more advanced topics.

Lessons:

• Introduction to schema
• Integers
• Decimals
• Strings
• Binary strings
• Long strings
• Enums
• Dates
• JSON
• Unexpected types
• Generated columns
• Schema migrations
• Schema recap

Section 2: Indexing in MySQL

In the next section of the course, we’ll cover indexing in depth. What is an index, and why is it so fast? How can we make the most of it when building our schema? We’ll cover all of these questions and more in this section.

Lessons:

• Introduction to indexes
• B+ trees
• Primary keys
• Secondary keys
• Primary key data types
• Where to add indexes
• Index selectivity
• Prefix indexes
• Composite indexes
• Covering indexes
• Functional indexes
• Indexing JSON columns
• Indexing for wildcard searches
• Fulltext indexes
• Invisible indexes
• Duplicate indexes
• Foreign keys

Section 3: Writing efficient and effective queries

In the third section of the course, we’ll cover querying. How do we write efficient and effective MySQL queries? How do we use the indexes we built earlier to make our queries super fast? This section builds on everything we’ve covered in the previous sections, so make sure you have a strong foundation before attempting it.

Lessons:

• Introduction to queries
• EXPLAIN overview
• EXPLAIN access types
• EXPLAIN ANALYZE
• Index obfuscation
• Reduntant and approximate conditions
• Select only what you need
• Limiting rows
• An overview of joins
• Indexing joins
• Subqueries
• Common table expressions (CTEs)
• Recursive CTEs
• Unions
• Window functions
• Sorting and limiting
• Sorting with indexes
• Sorting with composite indexes
• Counting results
• Dealing with NULLs

Section 4: Examples

In the final section of the course, we’ll take everything we’ve learned and put it together into a series of examples that application developers often encounter. By the end of this section, you’ll have a deep understanding of how to build efficient and effective MySQL databases that work well for your applications.

Lessons:

• Introduction to examples
• MD5 column
• MD5 over multiple columns
• Bitwise operations
• Timestamps versus booleans
• Claiming rows
• Summary tables
• Meta tables
• Offset limit pagination
• Cursor pagination
• Deferred joins
• Geographic searches
• Conclusion

Who this is for

This course is designed specifically for developers, so no prior knowledge of databases is required. We won’t be covering low-level details or administration, as this course is focused on how to use MySQL effectively as an application developer. If you’ve found databases intimidating in the past, don’t worry—we’ll take it step by step, and you’ll be an expert user by the end of the course.

Screen overview

Throughout the course, we’ll be using a TablePlus client on my Mac to run queries and view results. You’ll see me on screen sometimes, and most of the time, I’ll be up in the corner while we look at the TablePlus client. We won’t be using a lot of slides, as I know they can be boring. Instead, we’ll be getting hands-on and looking at actual tables, queries, indexes, and more within the TablePlus client.

Conclusion

This course covers everything you need to know to become an expert user of MySQL as a developer. We’ll start with the basics of schema and data types, move on to indexing and querying, and finish up with real-world examples. By the end of the course, you’ll have a deep understanding of how to build efficient and effective MySQL databases that work well for your applications. I hope you enjoy the course and learn a lot!