Cost-Based Optimizer

How the Cost-Based Optimizer Chooses a Plan

SQL says which rows you want, not how to fetch them. The cost-based optimizer first rewrites the statement (merging derived tables, turning IN subqueries into semijoins, propagating constants), then prices candidate access methods, join orders and algorithms, pruning a search that grows factorially with the tables. Its constants live in mysql.server_cost and mysql.engine_cost (on 9.7: 0.1 per row evaluated, 0.25 per page read from memory, 1.0 from disk). Row estimates come from index statistics (Index Statistics), index dives that count a range in the B+tree, and histograms (Join Order).

From SQL text to an executable plan, and the statistics the cost model reads

The per-session optimizer trace records each decision as JSON. Keep two traces and flatten their range analysis with JSON_TABLE (JSON_TABLE):

Watching the optimizer reject and then accept a range scanSQL

SET optimizer_trace = 'enabled=on', optimizer_trace_offset = -2, optimizer_trace_limit = 2;
SELECT COUNT(*) INTO @n FROM orders WHERE customer_id BETWEEN 1000 AND 30000 AND status = 'paid';
SELECT COUNT(*) INTO @n FROM orders WHERE customer_id BETWEEN 1000 AND 3000 AND status = 'paid';
SELECT jt.* FROM information_schema.OPTIMIZER_TRACE,
  JSON_TABLE(TRACE->'$**.range_analysis', '$[*]' COLUMNS (
    scan_cost DOUBLE PATH '$.table_scan.cost',
    NESTED PATH '$.analyzing_range_alternatives.range_scan_alternatives[*]' COLUMNS (
      range_scanned VARCHAR(30) PATH '$.ranges[0]', est_rows INT PATH '$.rows',
      range_cost DOUBLE PATH '$.cost', chosen VARCHAR(5) PATH '$.chosen'))) AS jt;

Output

Query OK, 0 rows affected (0.000 sec)
Query OK, 1 row affected (0.099 sec)
Query OK, 1 row affected (0.019 sec)
+-----------+------------------------------+----------+------------+--------+
| scan_cost | range_scanned                | est_rows | range_cost | chosen |
+-----------+------------------------------+----------+------------+--------+
|   50204.7 | 1000 <= customer_id <= 30000 |   246594 |    86308.2 | false  |
|   50204.7 | 1000 <= customer_id <= 3000  |    18146 |    6351.36 | true   |
+-----------+------------------------------+----------+------------+--------+
2 rows in set (0.002 sec)

Each row found through a secondary index needs a second lookup in the clustered index (Clustered Indexes), so about 246,594 of them (really 145,683) cost more than one pass over all 499,223 rows; the narrow range won. Range costs vary between runs (271,081 on a colder one) because cached pages count as cheaper. The trace shows whether a bad plan came from the estimate or the cost.

MENU