Chapter 09 · Optimizer, EXPLAIN, Statistics, and Query Plan Engineering
How the MySQL Optimizer Builds Access Paths, Join Orders, and Cost Estimates
Treat the MySQL optimizer as a cost-based decision system: understand candidate access paths, cardinality/selectivity estimates, join-order search, and why the chosen plan can change as data, indexes, or statistics change.
Learning outcomes
Chapter 08 taught you to design candidate indexes from workload evidence. Chapter 09 begins where index design stops: MySQL still has to decide which access path to use, in which table order, and with which join strategy. The optimizer does not read a query as a procedural script. It compares alternatives using estimates and a cost model, then chooses one plan before execution.
Explain the roles of candidate access paths, selectivity/cardinality estimates, join-order search, and cost estimates without treating cost as elapsed time.
Demonstrate that SQL FROM-clause order does not normally dictate execution order for inner joins.
Read SHOW INDEX and EXPLAIN evidence to connect statistics to an optimizer choice.
Reproduce a plan/access-path change after a purposeful index/statistics change and verify the actual work with EXPLAIN ANALYZE.
Reject folklore fixes such as blindly forcing an index when the root problem is a missing or poorly shaped access path.
A realistic problem: the same SQL got slower
ServiceHub has a dispatcher report that joins customers to recent work orders. It was fast when the database was small. After several months of data growth, the report starts scanning far more rows. The SQL text did not change, so a developer concludes that “MySQL changed its mind randomly” and proposes forcing the old index forever.
That diagnosis skips the optimizer’s actual job. A candidate access path is one possible way to obtain rows from a table: a full scan, an index lookup, a range scan, and so on. Cardinality and selectivity estimates describe how many rows MySQL expects predicates and joins to retain. Join order is the order in which table iterators are combined. Cost is an internal comparative estimate—not a promise of milliseconds.
Think of optimization as planning before execution: enumerate legal strategies, estimate how many rows flow through them, estimate relative resource cost, and choose the cheapest candidate according to the server cost model. EXPLAIN shows the chosen estimate; EXPLAIN ANALYZE later shows what actually happened.
Build the disposable plan lab
The lab creates 3,000 customers, 30,000 work orders, and a small activity stream. Status and channel values are deliberately skewed so later lessons can demonstrate estimation errors. All mandatory work runs on a local Community Server; no cloud or Enterprise feature is required.
DROP DATABASE IF EXISTS servicehub_plan_lab;CREATE DATABASE servicehub_plan_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_plan_lab;CREATE TABLE customers ( customer_id BIGINT UNSIGNED NOT NULL, tenant_id INT UNSIGNED NOT NULL, segment VARCHAR(16) NOT NULL, region_code VARCHAR(12) NOT NULL, annual_value DECIMAL(12,2) NOT NULL, created_at DATE NOT NULL, PRIMARY KEY (customer_id), KEY ix_customer_tenant_segment (tenant_id, segment, customer_id)) ENGINE=InnoDB;CREATE TABLE work_orders ( work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id INT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, technician_id BIGINT UNSIGNED NULL, status VARCHAR(16) NOT NULL, priority TINYINT UNSIGNED NOT NULL, channel VARCHAR(16) NOT NULL, opened_at DATETIME(6) NOT NULL, closed_at DATETIME(6) NULL, estimated_cost DECIMAL(10,2) NOT NULL, summary VARCHAR(180) NOT NULL, PRIMARY KEY (work_order_id), KEY ix_wo_customer (customer_id), KEY ix_wo_tenant_opened (tenant_id, opened_at, work_order_id), KEY ix_wo_technician (technician_id, opened_at, work_order_id), CONSTRAINT fk_wo_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)) ENGINE=InnoDB;CREATE TABLE activities ( activity_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, work_order_id BIGINT UNSIGNED NOT NULL, activity_type VARCHAR(16) NOT NULL, created_at DATETIME(6) NOT NULL, note VARCHAR(180) NOT NULL, PRIMARY KEY (activity_id), KEY ix_activity_workorder_type (work_order_id, activity_type, created_at), CONSTRAINT fk_activity_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;SET SESSION cte_max_recursion_depth = 50000;INSERT INTO customers (customer_id, tenant_id, segment, region_code, annual_value, created_at)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 3000)SELECT n, 1 + MOD(n-1,50), CASE WHEN MOD(n,20)=0 THEN 'enterprise' WHEN MOD(n,5)=0 THEN 'growth' ELSE 'standard' END, ELT(1+MOD(n,4),'north','south','east','west'), 1000 + MOD(n*7919,250000), DATE('2024-01-01') + INTERVAL MOD(n,700) DAYFROM seq;INSERT INTO work_orders (tenant_id,customer_id,technician_id,status,priority,channel,opened_at,closed_at,estimated_cost,summary)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 30000)SELECT 1 + MOD(n-1,50), 1 + MOD(n-1,3000), CASE WHEN MOD(n,13)=0 THEN NULL ELSE 100 + MOD(n,400) END, CASE WHEN MOD(n,100) < 80 THEN 'closed' WHEN MOD(n,100) < 90 THEN 'open' WHEN MOD(n,100) < 96 THEN 'waiting' ELSE 'cancelled' END, 1 + MOD(n,4), CASE WHEN MOD(n,100) < 92 THEN 'portal' WHEN MOD(n,100) < 98 THEN 'phone' ELSE 'monitor' END, TIMESTAMP('2026-01-01 00:00:00') + INTERVAL MOD(n,210) DAY + INTERVAL MOD(n*37,86400) SECOND, CASE WHEN MOD(n,100) < 80 THEN TIMESTAMP('2026-01-01 00:00:00') + INTERVAL MOD(n,210) DAY + INTERVAL (MOD(n*37,86400)+3600) SECOND ELSE NULL END, 25 + MOD(n*97,5000), CONCAT('ServiceHub work order ',n)FROM seq;INSERT INTO activities (work_order_id,activity_type,created_at,note)SELECT work_order_id, CASE WHEN MOD(work_order_id,10)<6 THEN 'note' WHEN MOD(work_order_id,10)<9 THEN 'status' ELSE 'dispatch' END, opened_at + INTERVAL 15 MINUTE, CONCAT('First activity for work order ',work_order_id)FROM work_orders;INSERT INTO activities (work_order_id,activity_type,created_at,note)SELECT work_order_id,'note',opened_at + INTERVAL 45 MINUTE, CONCAT('Follow-up activity for work order ',work_order_id)FROM work_ordersWHERE MOD(work_order_id,3)=0;ANALYZE TABLE customers, work_orders, activities;USE servicehub_plan_lab;SELECT COUNT(*) AS customers FROM customers;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT status,COUNT(*) AS n FROM work_orders GROUP BY status ORDER BY n DESC;SELECT channel,COUNT(*) AS n FROM work_orders GROUP BY channel ORDER BY n DESC;SHOW INDEX FROM work_orders;Expected state: 3,000 customers and 30,000 work orders exist; closed dominates status and portal dominates channel. Exact optimizer cardinality values are estimates and can differ slightly after statistics sampling, so do not confuse SHOW INDEX.Cardinality with the exact counts above.
SQL text order is not execution order
The following query deliberately names customers first. The optimizer is free to begin with work_orders if that route is estimated cheaper for this inner join.
EXPLAIN FORMAT=TREESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;On the initial schema, ix_wo_tenant_opened can constrain tenant and date, but status is not a key part. Your TREE output may show a range scan with an additional filter, followed by primary-key lookups into customers. Record the plan on your machine instead of memorizing a specific row estimate.
| Evidence | What it means | What it does not mean |
|---|---|---|
| possible_keys / candidate indexes | Indexes that may be usable for the table reference | That every listed index will be read |
| key / named iterator | The access path selected for this plan | That this index is always best for every parameter value |
| estimated rows | Optimizer prediction for rows produced by an iterator | Exact runtime row count |
| cost | Relative optimizer cost estimate within the model | Milliseconds, CPU percentage, or billing units |
| actual rows / loops | Observed iterator output during EXPLAIN ANALYZE | A universal result independent of cache, data, concurrency, or version |
Change one physical fact and re-plan
Instead of forcing an existing index, add one that matches the actual predicate/order shape. This changes the optimizer’s set of legal candidates.
CREATE INDEX ix_wo_tenant_status_opened ON work_orders (tenant_id,status,opened_at,customer_id,work_order_id);ANALYZE TABLE work_orders;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_tenant_status_opened';EXPLAIN FORMAT=TREESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;Expected qualitative change: the new key gives MySQL a narrower range that combines tenant, status, and date. If the optimizer selects it, fewer work-order rows should flow into the join. The exact timing can vary with cache and hardware; the durable evidence is the access method, estimated versus actual row flow, and repeated local measurements under comparable conditions.
The tempting wrong fix: force before understanding
A common reaction to a surprising plan is to add FORCE INDEX immediately. A forced path can make today’s parameter value look better while hiding a stale-statistics problem, a missing composite key, or a workload that changed. It can also become harmful as data distribution changes.
EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w FORCE INDEX (ix_wo_tenant_opened) ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;-- Compare with the unforced statement immediately afterward.EXPLAIN ANALYZESELECT c.customer_id,c.segment,w.work_order_id,w.opened_atFROM customers AS cJOIN work_orders AS w ON w.customer_id=c.customer_idWHERE w.tenant_id=17 AND w.status='open' AND w.opened_at >= '2026-05-01'ORDER BY w.opened_at DESCLIMIT 40;If the forced plan processes more rows or takes longer in repeated local runs, the experiment disproves the slogan that “using an index is always faster.” Even if it wins once, do not promote the force into application SQL until you can explain the estimate error and test representative parameter ranges.
Plan engineering starts with representative SQL, bind-value distributions, table/index statistics, and a known baseline. Do not optimize a single screenshot. Capture query shape, schema/index definitions, data scale, estimates, actual rows/loops, cache/concurrency context, and server version so a future engineer can reproduce the decision.
Hands-on lab and verification
Choose three tenant IDs: one with many qualifying rows, one with few, and one with none in the selected date range. Run the unforced query with EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE for each. Record whether the same access path remains sensible across the parameter set.
SELECT VERSION() AS server_version, @@version_comment AS edition_comment;SHOW VARIABLES LIKE 'optimizer_switch';SHOW INDEX FROM work_orders;ANALYZE TABLE customers,work_orders;SELECT tenant_id,status,COUNT(*) AS nFROM work_ordersWHERE opened_at >= '2026-05-01'GROUP BY tenant_id,statusORDER BY n DESCLIMIT 20;Knowledge check
- Does the first table written after FROM have to execute first?
- Is EXPLAIN cost a prediction of milliseconds?
- Why can adding a composite index change the plan?
- What is the first evidence to capture when a query regresses?
- Why is FORCE INDEX a poor first response?
Reveal answers
- No. For reorderable inner joins, MySQL can choose a different join order when its cost model estimates that order is cheaper.
- No. Cost is an internal comparative estimate used by the optimizer; it is not elapsed wall-clock time.
- It changes the candidate access paths and the estimated amount of work needed to satisfy the predicates/order.
- The exact query shape/parameters, schema and index state, EXPLAIN plan, actual execution evidence where safe, statistics state, data scale/distribution, and server version.
- It constrains optimizer choice without fixing the underlying estimate or access-path problem and can age badly as data changes.
Summary and next step
The optimizer is deterministic engineering machinery operating on imperfect information, not a random oracle. You have now changed a plan by changing a real physical candidate rather than by superstition. Lesson 2 focuses on the diagnostic language of plans themselves—especially the difference between estimates and iterator evidence from EXPLAIN ANALYZE.