Learn MySQL: The Best Database Books, in Order
This curriculum takes a beginner from zero SQL knowledge all the way to expert-level MySQL performance tuning, in four carefully sequenced stages. Each stage builds directly on the last — first establishing relational thinking and SQL fluency, then MySQL-specific development skills, then schema and query design mastery, and finally the internals and tuning techniques that separate good developers from great ones.
Foundations: Relational Thinking & SQL Basics
BeginnerUnderstand how relational databases work conceptually and write confident, correct SQL queries — SELECT, JOIN, GROUP BY, subqueries, and basic DML (INSERT, UPDATE, DELETE).
▸ Study plan for this stage
Pace: 8–10 weeks, ~25–30 pages/day. Start with "Learning SQL" (weeks 1–5, ~200 pages), then move to "SQL Queries for Mere Mortals" (weeks 6–10, ~250 pages). Allocate 2–3 days per major chapter for practice and review.
- Relational model fundamentals: tables, rows, columns, keys (primary, foreign), and relationships (one-to-many, many-to-many)
- Database normalization principles and why they matter for data integrity and query efficiency
- SELECT statement anatomy: FROM, WHERE, ORDER BY, and how to filter and retrieve data accurately
- JOIN operations: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN to combine data from multiple tables
- Aggregation and grouping: GROUP BY, HAVING, aggregate functions (COUNT, SUM, AVG, MIN, MAX) for summarizing data
- Subqueries and derived tables: scalar subqueries, correlated subqueries, and IN/EXISTS operators for complex filtering
- DML fundamentals: INSERT, UPDATE, DELETE with proper WHERE clauses and transaction awareness
- Query logic and execution order: understanding how SQL engines process queries to write efficient, correct code
- What is a primary key and a foreign key, and how do they enforce relational integrity?
- Explain the difference between INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN with concrete examples.
- Write a query that uses GROUP BY and HAVING to find customers who placed more than 3 orders.
- What is a correlated subquery, and when would you use it instead of a JOIN?
- How would you safely update or delete records in a table with foreign key constraints?
- Describe the order in which SQL processes clauses (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) and why it matters.
- Create a simple 3–4 table relational schema (e.g., customers, orders, products) with appropriate primary and foreign keys; write the CREATE TABLE statements.
- Write 10+ SELECT queries of increasing complexity: simple WHERE filters, multi-table JOINs, GROUP BY aggregations, and subqueries against your schema.
- Practice all JOIN types: write queries using INNER, LEFT, RIGHT, and FULL OUTER JOINs on your schema and explain the row count differences.
- Build a query that uses a correlated subquery to find, e.g., 'orders above the customer's average order value' and rewrite it as a JOIN to compare approaches.
- Write INSERT, UPDATE, and DELETE statements on your schema; deliberately test constraint violations and understand the errors.
- Analyze 5–10 real-world SQL queries (from documentation or Stack Overflow examples) and trace through their execution order to predict results before running them.
Next up: This stage equips you with the conceptual and practical foundation to understand how MySQL stores and retrieves data; the next stage will build on this by teaching you optimization techniques (indexing, query execution plans, performance tuning) and advanced MySQL-specific features (stored procedures, triggers, transactions).
The single best beginner SQL book — clear, practical, and uses MySQL as its primary database engine. It builds relational intuition from scratch before introducing syntax, so nothing feels arbitrary.
Reinforces SQL thinking through hundreds of worked examples and exercises. Reading this after Beaulieu cements query-writing fluency and exposes common beginner mistakes before they become habits.
MySQL in Practice: Core Development Skills
BeginnerWork confidently with MySQL specifically — data types, storage engines, transactions, stored procedures, triggers, views, and using MySQL from application code.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day. "MySQL Crash Course" (weeks 1–4, ~25 pages/day), then "MySQL Cookbook" (weeks 5–10, ~40 pages/day with hands-on practice)
- MySQL data types (numeric, string, date/time, spatial) and choosing the right type for your data
- Storage engines (InnoDB vs. MyISAM) and their trade-offs for transactions, locking, and performance
- ACID transactions: BEGIN, COMMIT, ROLLBACK, and isolation levels to ensure data consistency
- Stored procedures and functions: writing reusable logic in SQL with parameters, variables, and control flow
- Triggers: automating actions (INSERT, UPDATE, DELETE) to enforce business rules and maintain data integrity
- Views: creating virtual tables for abstraction, security, and simplifying complex queries
- Connecting to MySQL from application code (PHP, Python, Node.js examples) and managing connections safely
- Query optimization and indexing strategies to improve performance in real-world applications
- When would you use VARCHAR vs. TEXT, and what are the performance implications of each?
- Explain the difference between InnoDB and MyISAM storage engines. When would you choose one over the other?
- What is an ACID transaction, and how do BEGIN, COMMIT, and ROLLBACK work together to maintain data integrity?
- Write a stored procedure that accepts parameters and uses conditional logic. What are the benefits of using stored procedures in your application?
- How do triggers differ from stored procedures, and what are some practical use cases for triggers (e.g., audit logging, cascading updates)?
- Design a view that simplifies a complex multi-table query. How does using a view improve security and maintainability?
- How do you safely connect to MySQL from application code, and what precautions should you take against SQL injection?
- Create a small e-commerce database schema using appropriate data types (INT, VARCHAR, DECIMAL, DATETIME). Justify your choices for each column.
- Build two identical tables—one with InnoDB and one with MyISAM. Run concurrent transactions on the InnoDB table to observe locking behavior and compare with MyISAM.
- Write a stored procedure that calculates order totals with tax, accepts a customer ID as input, and returns the result. Test it with different parameter values.
- Create a trigger that automatically updates a 'last_modified' timestamp whenever a row is updated, and another trigger that logs deletions to an audit table.
- Design and implement a view that joins three tables (e.g., customers, orders, order_items) to show order summaries. Query the view and verify it simplifies the application code.
- Write a transaction that transfers money between two accounts (UPDATE two tables) with ROLLBACK on error. Test both success and failure scenarios.
- Connect to your MySQL database from application code (Python with mysql-connector-python or PHP with mysqli). Execute a parameterized query to prevent SQL injection, then intentionally test with malicious input to see the difference.
- Identify a slow query in your database using EXPLAIN. Add an appropriate index and measure the performance improvement.
Next up: This stage equips you with hands-on mastery of MySQL's core features and best practices, preparing you to tackle advanced topics like replication, clustering, performance tuning at scale, and architectural patterns for high-availability systems.
A fast, focused introduction to MySQL's own syntax and tooling. Its brevity makes it ideal as a first MySQL-specific read right after general SQL foundations are in place.
A problem-solution reference covering the full breadth of everyday MySQL development tasks — importing data, working with dates, string handling, transactions, and more. Reading it sequentially after Forta fills in practical gaps quickly.
Schema Design & Query Mastery
IntermediateDesign normalized, maintainable database schemas, model real-world domains correctly, and write sophisticated queries that are both correct and efficient.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day (mix of reading and active schema/query work)
- Normalization theory (1NF, 2NF, 3NF, BCNF) and when to denormalize intentionally
- Entity-Relationship (ER) modeling and translating real-world domains into relational schemas
- Primary keys, foreign keys, and referential integrity as structural foundations
- Common SQL antipatterns (ambiguous groups, implicit columns, NULL handling) and how to avoid them
- Query design patterns: proper JOINs, subqueries vs. CTEs, aggregation logic, and performance implications
- Index strategy and query optimization fundamentals tied to schema design decisions
- Data type selection and constraint design to enforce business rules at the database level
- Recognizing and refactoring problematic schemas and queries from real-world codebases
- What are the five normal forms, and how do you identify when a schema violates each one? When is denormalization justified?
- How do you translate a complex real-world domain (e.g., an e-commerce system, hospital records) into a normalized ER diagram with proper cardinality and relationships?
- What are the most common SQL antipatterns (e.g., SELECT *, implicit columns, NULL in aggregate functions), and how do you rewrite queries to avoid them?
- Given a poorly designed schema or slow query, how do you diagnose the root cause and refactor it for correctness and performance?
- How do primary keys, foreign keys, and constraints work together to maintain data integrity, and what happens when they're missing or misconfigured?
- When should you use JOINs vs. subqueries vs. CTEs, and how does your schema design influence that choice?
- Design a normalized schema for a real-world domain (e.g., library management, airline reservations, social media platform) from scratch; document assumptions and justify each table, column, and relationship.
- Take a provided denormalized or poorly designed schema and refactor it to 3NF or BCNF; document what violations existed and how you resolved them.
- Write 10–15 sophisticated queries against your designed schema (multi-table JOINs, aggregations, window functions, CTEs) and explain the query plan for each.
- Identify and rewrite 5–10 SQL antipatterns from real code samples or exercises in the books; explain why each original query was problematic.
- Create an index strategy for your schema; predict which queries will benefit and measure (or simulate) performance impact.
- Peer-review or self-review a colleague's schema design and query code using normalization and antipattern checklists; document findings and suggest improvements.
Next up: This stage equips you with the discipline to build schemas that scale and queries that perform, setting the foundation for advanced topics like transaction management, concurrency control, and production optimization in the next stage.
The definitive beginner-to-intermediate guide to relational schema design — normalization, entity-relationship modeling, and avoiding classic design pitfalls. Establishes the design vocabulary needed for everything that follows.
Catalogs the most common mistakes developers make in schema design and query writing, with MySQL-relevant examples throughout. Reading this after learning correct design makes the anti-patterns immediately recognizable and avoidable.
Indexing, Internals & Performance Tuning
ExpertUnderstand how MySQL executes queries under the hood, design indexes strategically, interpret EXPLAIN output, tune configuration, and optimize schemas and queries for high-performance production systems.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day with hands-on lab work (2–3 hours/week)
- Query execution fundamentals: how MySQL parses, optimizes, and executes queries; the role of the query optimizer and execution engine
- Index structure and behavior: B-tree organization, leaf nodes, internal nodes, and how indexes support range queries and sorting
- EXPLAIN output interpretation: reading and analyzing execution plans to identify full table scans, index usage, join order, and estimated costs
- Index design strategy: choosing columns for indexes, multi-column indexes, index selectivity, and avoiding common pitfalls (redundant indexes, wrong column order)
- Query optimization techniques: rewriting queries, using covering indexes, optimizing joins, and eliminating subqueries where beneficial
- Schema and configuration tuning: buffer pool sizing, query cache behavior, sort buffer, join buffer, and their impact on performance
- Cost-based optimization: understanding how the optimizer estimates costs, cardinality, and why it sometimes chooses suboptimal plans
- Performance bottleneck diagnosis: identifying slow queries, measuring query cost, and distinguishing between I/O-bound and CPU-bound problems
- How does MySQL's query optimizer work, and what information does it use to choose an execution plan?
- What is a B-tree index, how are rows stored and retrieved using it, and why does column order matter in multi-column indexes?
- How do you read and interpret EXPLAIN output, and what do key metrics like 'type', 'key', 'rows', and 'Extra' tell you about query performance?
- What makes an index selective and efficient, and when is a column a poor choice for indexing?
- How can you rewrite a query or design an index to avoid a full table scan or expensive join operation?
- What is a covering index, and how does it eliminate the need for lookups to the clustered index?
- How do buffer pool, sort buffer, and join buffer settings affect query performance, and how do you tune them?
- How do you identify and measure slow queries, and what tools and techniques help diagnose performance bottlenecks?
- Set up a local MySQL instance and create a test database with 100k–1M rows across 3–5 tables; run EXPLAIN on 10 different queries and document the execution plans
- Design and implement a multi-column index on a table; measure query performance before and after using BENCHMARK() or query timing; explain why the index helped or didn't
- Identify a slow query using slow query log or performance_schema; analyze its EXPLAIN output, rewrite the query, and measure the improvement
- Create a covering index for a frequently-run SELECT query; verify that the query uses the index and avoids clustered index lookups
- Experiment with buffer pool size, sort buffer size, and join buffer size; run the same query workload with different settings and measure execution time
- Write a query with a subquery; rewrite it using a JOIN and compare EXPLAIN output and execution time
- Analyze index selectivity: create two indexes on the same table (one on a low-selectivity column, one on a high-selectivity column); run queries and observe which index the optimizer chooses
- Use ANALYZE TABLE and SHOW STATISTICS to inspect cardinality estimates; run a query where the optimizer's estimate is wrong and document the impact on the execution plan
Next up: This stage equips you with deep knowledge of how MySQL executes queries and how to optimize them at the index, schema, and configuration level—skills essential for the next stage, which will likely focus on scaling strategies (replication, sharding, caching layers) and architectural patterns for high-concurrency, distributed systems.
The canonical advanced MySQL book — covers InnoDB internals, indexing strategy, query optimization, replication, and scaling. This is the book the MySQL community universally points to for going from competent to expert.
A deep, database-agnostic treatment of index design that perfectly complements High Performance MySQL. It teaches you to reason about indexes from first principles, making you far more effective at tuning any MySQL workload.
Discussion
Keep reading
Paths that share books, cover the same subject, or open a related topic.
