High Performance MySQL [Ch.6: Query Performance Optimization]

Why a query is slow

A query is a sequence of tasks: parse, plan, execute, send results. Execution dominates, because that is where the server calls into the storage engine for rows and then sorts, groups and filters them. Making a query faster means removing tasks, running them less often, or making them cheaper, and the biggest lever is almost always the amount of data touched.

Is the application asking for too much?

• More rows than it shows. MySQL builds the whole result even if the client reads only the first ten. Use LIMIT.
• Every column. SELECT * drags full rows through joins, and it rules out covering indexes. Name the columns you use.
• The same data, repeatedly. Fetching a user's name, then email, then avatar in three queries is three round trips for one row.
• N+1 queries. ORMs that lazy-load associations turn "list projects and their bugs" into one query plus one per project. Load the association eagerly with a single WHERE project_id IN (...).

Is the server examining too much?

Three numbers describe a query's cost, and all three appear in the slow query log: response time, rows examined and rows returned. Response time is the noisiest. It includes waiting for locks and I/O and moves with whatever else the server is doing. The ratio of rows examined to rows returned is more telling: 1:1 is ideal, joins and aggregates legitimately raise it, and a ratio in the thousands means the server is reading rows only to throw them away.

The best predictor is the access type, the type column in EXPLAIN:

The Extra column tells you where filtering happens. Using index means a covering index answered the query without touching rows. Using index condition means the engine filtered on index columns before fetching rows. Using where means rows were fetched and then filtered by the server, which is the expensive way.

When the access type is wrong, the fix is an index the query can use, a query rewritten to use an existing one, or a schema change such as a summary table.

Restructuring queries

The aim is the same answer for less work, or occasionally a slightly different answer that is acceptable and far cheaper.

One complex query or several simple ones? The old rule was to minimize round trips. MySQL connects and answers small queries quickly, and networks inside a data centre are fast, so splitting work is often fine and easier to maintain. Weigh it case by case.

Chop up big writes. A single DELETE of millions of old rows holds locks for its whole run, bloats the undo log and stalls replicas. Delete in batches instead, and pause between them if needed:

-- one statement, one huge transaction
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR;

-- repeat until it affects 0 rows
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR LIMIT 5000;

Decompose joins. Running one query per table and joining in the application can help when results are cacheable (a tag's id rarely changes), when shorter queries reduce lock contention, or when an IN (...) list of sorted ids reads rows more sequentially than a nested loop would. It is a tool, not a default.

From parse tree to plan

The parser turns SQL into a tree and rejects syntax errors. The preprocessor checks meaning the grammar cannot: that tables and columns exist, that names are unambiguous, and that the user has the privileges for the statement. Then the optimizer searches for a cheap plan.

MySQL's optimizer is cost-based with some rule-based shortcuts. It estimates the cost of candidate plans from statistics (table and index sizes, index cardinality, key distribution) and picks the cheapest. It can choose a worse plan for predictable reasons: statistics are estimates and InnoDB's row counts are approximate; its cost model does not know which pages are already cached; and it cannot see the cost of work it does not control, such as stored functions. The estimated cost of the last query is in Last_query_cost.

What the optimizer does for you

• Reorders joins to start from the table that produces the fewest rows.
• Turns outer joins into inner joins when the WHERE clause rejects the NULL rows anyway.
• Simplifies expressions: 5 = 5 AND a > 5 becomes a > 5, and impossible conditions end the query early.
• Resolves constants first. A primary-key lookup in one table becomes a constant used by the rest of the plan.
• Propagates equalities across join columns, so a filter on one table also narrows the other.
• Answers MIN() and MAX() from an index edge: one read at the start or end of the B-Tree.
• Sorts IN() lists and binary-searches them instead of scanning.
• Stops early for LIMIT, DISTINCT and NOT EXISTS once the answer is known.
• Rewrites subqueries into semi-joins or index lookups where it can.

How MySQL executes joins

MySQL treats every multi-table operation, including derived tables and UNION, as a join, and its traditional algorithm is the nested loop: for each row of the first table, find matching rows in the second; for each of those, find matches in the third; emit a row when every table has matched, then backtrack. A LEFT JOIN is the same loop that emits NULLs when the inner table has no match; RIGHT JOIN is rewritten as a left join. The model cannot express FULL OUTER JOIN, which MySQL still does not support. Since MySQL 8.0.18, equi-joins with no usable index run as a hash join instead of a nested loop.

Because cost is "rows in the outer table × lookups per row", join order matters enormously. Joining 5,000 students to their grades costs 5,000 index lookups into grades, whichever way you write the FROM clause; the optimizer picks the order with the fewest probes. STRAIGHT_JOIN forces your written order, which is useful for testing and occasionally for overruling a bad estimate. With many tables the number of possible orders explodes (10 tables already have 3,628,800), so beyond optimizer_search_depth MySQL switches to a greedy search.

The finished plan is not bytecode. It is a tree of operations the executor walks, which EXPLAIN FORMAT=TREE shows directly.

How this shows up in your verdict

Every challenge is graded on rows examined against a reference plan, which is this chapter in one number. Joining a table you do not need, grouping rows that are already unique, or filtering after a scan instead of during an index lookup all show up as extra handler reads, and as CORRECT_BUT_INEFFICIENT.

Common questions