MENU


3. MySQL

You can use MySQL for years knowing only SELECT, INSERT, UPDATE and DELETE, and then lose a weekend to a query that took 20 ms on your laptop and 40 seconds in production. The gap between the two is the subject of this chapter. It teaches SQL the way the server actually runs it: how a SELECT is evaluated, what an index really is, how the optimizer picks a plan, what isolation level your transaction ran at, and why a GROUP BY that older servers accepted is now an error.The chapter runs from setup to operations. Sections 3.1 to 3.4 install the server and define tables and types; 3.5 to 3.9 cover querying, from joins and subqueries through window functions, JSON, full-text and spatial data; 3.10 to 3.13 deal with design, indexing, the optimizer and server tuning; 3.14 to 3.17 cover transactions, stored routines, accounts and administration; and 3.18 moves the database to the cloud and compares MySQL with its rivals. Most examples query one small bookshop schema of customers, products, orders and reviews, so the chapter reads as one story.Everything here runs against MySQL 9.7 LTS, the current long-term-support series. PHP's side of the conversation (PDO, bound parameters and connection handling) is in Chapter 4, and Laravel's Eloquent and migrations are in Chapter 5. MongoDB appears only for comparison, since Volume 2 covers it in depth.What you will learn

  • How MySQL's LTS and Innovation tracks work, and how to install 9.7 LTS on Linux, Windows, macOS and Docker

  • SQL from CREATE TABLE to recursive CTEs, window functions, JSON and full-text search

  • How to choose data types and constraints, and how to normalize a schema

  • How InnoDB indexes work, how to read EXPLAIN, and how to tune the server

  • Transactions, locking, stored routines, users, roles, backups and replication