MENU


The Optimizer, EXPLAIN, and Query Tuning

The optimizer decides whether a query uses the indexes of Section 3.11. This section reads, measures and corrects its plans on shop grown with a recursive CTE (Section 3.6.8) and seeded RAND(): 100,000 customers (45% in the US), 10,000 products, 500,000 orders (5% cancelled), 1,249,994 order lines and 200,000 reviews, indexed only by the declared keys. Timings come from a WSL2 machine (Ubuntu 26.04, MySQL 9.7.2, default 128 MB buffer pool); expect yours to differ.

3.12.1 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 (Section 3.11.6), index dives that count a range in the B+tree, and histograms (Section 3.12.7).Figure 3.11: From SQL text to an executable plan, and the statistics the cost model readsThe per-session optimizer trace records each decision as JSON. Keep two traces and flatten their range analysis with JSON_TABLE (Section 3.9.5):

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;

Listing 3.106: Watching the optimizer reject and then accept a range scan

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)

Output of Listing 3.106 Each row found through a secondary index needs a second lookup in the clustered index (Section 3.11.2), 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.

3.12.2 Reading EXPLAIN Output

EXPLAIN shows the plan without running the statement. Since 9.5 the default explain_format is TREE, so a plain EXPLAIN prints a tree, not the table older tutorials show. FORMAT=TRADITIONAL gives the table; cut keeps nine of its twelve columns so it fits a terminal:

q="SELECT o.id, o.ordered_at, c.name FROM customers AS c JOIN orders AS o

ON o.customer_id = c.id WHERE c.country = 'SE' AND o.status = 'cancelled'"

sudo mysql shop -Nre "EXPLAIN $q"

sudo mysql shop -e "EXPLAIN FORMAT=TRADITIONAL $q" | cut -f3,5-12 | column -t -s $'\t'

Listing 3.107: The same plan as a tree (the 9.7 default) and as the traditional table

-> Nested loop inner join (cost=27369 rows=12261)

-> Filter: (c.country = 'SE') (cost=10204 rows=9730)

-> Table scan on c (cost=10204 rows=97302)

-> Filter: (o.`status` = 'cancelled') (cost=1.26 rows=1.26)

-> Index lookup on o using customer_id (customer_id = c.id) (cost=1.26 rows=5.04)

table type possible_keys key key_len ref rows filtered Extra

c ALL PRIMARY NULL NULL NULL 97302 10.00 Using where

o ref customer_id customer_id 4 shop.c.id 5 25.00 Using where

Output of Listing 3.107 Each tree node pulls rows from the nodes indented beneath it. A nested loop runs its first child once and its second once per row of the first, so c is the driving table, and o is probed about 9,730 times. The table has one row per table in join order. type is the access method (Section 3.12.3), key the index chosen from possible_keys, and key_len the index bytes used, which on a composite index shows how many leading columns took part. ref is what the index is matched against, rows the estimate per scan or lookup, and filtered the percentage expected to survive the other conditions: 97,302 customers × 10% × 5 orders × 25% = 12,261 rows. Neither column is indexed, so both percentages are guesses; really 5.3% of customers are Swedish and 4.9% of orders cancelled. SHOW WARNINGS after EXPLAIN shows the statement as the optimizer rewrote it.

3.12.3 The type Column and Access Methods

type, how MySQL finds a table's rows, is the first column to check. This function prints five columns of the traditional plan (table, type, key, rows, Extra):

plan() { sudo mysql shop -N -e "EXPLAIN FORMAT=TRADITIONAL $1" | cut -f3,5,7,10,12; }

{

plan "SELECT * FROM orders WHERE id = 42"

plan "SELECT * FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id

WHERE oi.product_id = 7"

plan "SELECT * FROM orders WHERE customer_id BETWEEN 10 AND 20"

plan "SELECT * FROM orders WHERE customer_id = 5 OR id = 9"

plan "SELECT COUNT(*) FROM orders"

plan "SELECT * FROM orders WHERE status = 'pending'"

} | column -t -s $'\t'

Listing 3.108: A plan() helper, and one query per access method

orders const PRIMARY 1 NULL

oi ref product_id 52 NULL

o eq_ref PRIMARY 1 NULL

orders range customer_id 62 Using index condition

orders index_merge customer_id,PRIMARY 7 Using union(customer_id,PRIMARY); Using where

orders index customer_id 499223 Using index

orders ALL NULL 499223 Using where

Output of Listing 3.108 From best to worst: const reads one row by a unique key, once, at the start; eq_ref one row per outer row by a unique key, the ideal inner side of a join; ref the rows matching a non-unique index; range a bounded slice (<, BETWEEN, IN, LIKE 'x%'); index_merge a union of lookups on two indexes; index a whole index (COUNT(*) picks the smallest); ALL the whole table. Down to range, cost follows the rows you ask for. index and ALL follow table size, harmless on a lookup table and the first thing to fix on a large driving table.

3.12.4 The Extra Column

Extra reports work beyond the access method and is often the most actionable column:

plan() { sudo mysql shop -N -e "EXPLAIN FORMAT=TRADITIONAL $1" | cut -f3,5,7,10,12; }

{

plan "SELECT customer_id FROM orders WHERE customer_id = 5"

plan "SELECT * FROM customers WHERE email LIKE 'user12%' AND name LIKE '%9'"

plan "SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC"

plan "SELECT MAX(id) FROM orders"

plan "SELECT COUNT(*) FROM reviews AS r JOIN orders AS o ON o.ordered_at = r.created_at"

} | column -t -s $'\t'

Listing 3.109: Five plans, five kinds of Extra

orders ref customer_id 6 Using index

customers range email 1111 Using index condition; Using where

orders ALL NULL 499223 Using temporary; Using filesort

NULL NULL NULL NULL Select tables optimized away

r ALL NULL 199366 NULL

o ALL NULL 499223 Using where; Using join buffer (hash join)

Output of Listing 3.109

  • Using index: the index covers the query (Section 3.11.5), so no row is read.

  • Using index condition: InnoDB tests the email prefix inside the index before fetching rows (index condition pushdown). Using where: the server filters rows, here on name LIKE '%9'.

  • Using temporary; Using filesort: grouping built an internal temporary table and ORDER BY needed a sort (in memory while sort_buffer_size suffices, Section 3.13.5). Both on a large ALL scan usually mean a missing index.

  • Select tables optimized away: MAX(id) was read from the edge of the index while planning.

  • Using join buffer (hash join): no index serves the join condition (Section 3.12.6).

    • Others include Backward index scan, Impossible WHERE, and the semijoin strategies FirstMatch and LooseScan (Section 3.6.3).

    3.12.5 EXPLAIN ANALYZE and FORMAT=JSON

    EXPLAIN ANALYZE executes the statement, discards the result, and prints measurements beside each estimate. The xa function moves each node's figures onto a second line:

    xa() { sudo mysql shop -Nre "EXPLAIN ANALYZE $1" |

    sed -E 's/^( *)(.*[^ ]) +(\(.*)$/\1\2\n\1 \3/'; }

    xa "SELECT COUNT(*) FROM orders WHERE customer_id BETWEEN 1000 AND 30000 AND status = 'paid'"

    sudo mysql shop -Nre "EXPLAIN ANALYZE FORMAT=JSON SELECT COUNT(*) FROM orders

    WHERE customer_id BETWEEN 1000 AND 30000 AND status = 'paid'" |

    jq -c '.query_plan.inputs[0] | {estimated_rows, actual_rows, actual_last_row_ms}'

    Listing 3.110: Estimates beside measurements, as a tree and as JSON

    -> Aggregate: count(0)

    (cost=64407 rows=1) (actual time=133..133 rows=1 loops=1)

    -> Filter: ((orders.`status` = 'paid') and (orders.customer_id between 1000 and 30000))

    (cost=50203 rows=61649) (actual time=1.56..132 rows=21780 loops=1)

    -> Table scan on orders

    (cost=50203 rows=499223) (actual time=1.55..90.6 rows=500000 loops=1)

    {"estimated_rows":61648.500645160675,"actual_rows":21780.0,"actual_last_row_ms":127.01337500000

    001}

    Output of Listing 3.110 actual time=1.56..132 is milliseconds to the first and last row, children included, so the filter added 41 ms to a 91 ms scan. rows is per loop; multiply by loops on the inner side of a join. Compare estimated with actual rows node by node: three times out is harmless, ten times out on a driving table usually means a bad join order (Section 3.12.7). JSON version 2, the default since 9.5, mirrors the tree and is the only JSON EXPLAIN ANALYZE accepts; version 1 is the query_block layout of 8.0-era tools. EXPLAIN FORMAT=JSON INTO @plan stores a plan in a variable (Section 3.9.6).

    3.12.6 Nested-Loop and Hash Joins

    A nested-loop join probes the inner table once per outer row, ideally through an index. A hash join loads the smaller input into a hash table and streams the larger one past it, reading each once. It arrived in 8.0.18, replaced block nested loop in 8.0.20, serves outer, semi- and antijoins too, spills to disk beyond join_buffer_size (256 KB), and obeys BNL/NO_BNL. The classic optimizer uses it only when no index serves the join, so this report over 1.25 million order lines needs hints to get one:

    xa() { sudo mysql shop -Nre "EXPLAIN ANALYZE $1" |

    sed -E 's/^( *)(.*[^ ]) +(\(.*)$/\1\2\n\1 \3/'; }

    q="p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue

    FROM order_items AS oi JOIN products AS p ON p.id = oi.product_id GROUP BY p.category_id"

    xa "SELECT /*+ NO_JOIN_INDEX(p PRIMARY) NO_JOIN_INDEX(oi product_id) */ $q"

    Listing 3.111: Forcing a hash join for a revenue report

    -> Table scan on <temporary>

    (actual time=1251..1251 rows=4 loops=1)

    -> Aggregate using temporary table

    (actual time=1251..1251 rows=4 loops=1)

    -> Inner hash join (oi.product_id = p.id)

    (cost=1.25e+9 rows=1.36e+6) (actual time=10.9..767 rows=1.25e+6 loops=1)

    -> Table scan on oi

    (cost=0.225 rows=1.25e+6) (actual time=4.01..394 rows=1.25e+6 loops=1)

    -> Hash

    -> Covering index scan on p using category_id

    (cost=1097 rows=10015) (actual time=1.09..5.7 rows=10000 loops=1)

    Output of Listing 3.111 Unhinted, the nested-loop plan took 2.4 to 9.3 s across runs. The hash join read each table once, in 1.25 s, yet was costed at 1.25e+9. The hypergraph optimizer, off by default and new to the 9.7 Community Edition, costs both properly: after SET optimizer_switch='hypergraph_optimizer=on' it chose the hash join itself, in 0.97 s. It prints only tree and JSON plans (TRADITIONAL fails with error 3999) and can slow simple queries, so try it per session first.

    3.12.7 Join Order, Driving Tables, and STRAIGHT_JOIN

    FROM order does not decide execution order: the optimizer reorders inner joins (an outer join still reads its preserved side first) to drive from the input that shrinks the rows earliest, which is only as good as its estimates:

    SELECT COUNT(*) INTO @n FROM customers AS c JOIN orders AS o ON o.customer_id = c.id

    WHERE c.country = 'US' AND o.status = 'cancelled';

    SELECT STRAIGHT_JOIN COUNT(*) INTO @n FROM orders AS o JOIN customers AS c

    ON o.customer_id = c.id WHERE c.country = 'US' AND o.status = 'cancelled';

    ANALYZE TABLE customers UPDATE HISTOGRAM ON country;

    ANALYZE TABLE orders UPDATE HISTOGRAM ON status;

    EXPLAIN SELECT COUNT(*) FROM customers AS c JOIN orders AS o ON o.customer_id = c.id

    WHERE c.country = 'US' AND o.status = 'cancelled'\G

    Listing 3.112: A wrong driving table, forced right, then fixed with histograms

    Query OK, 1 row affected (0.753 sec)

    Query OK, 1 row affected (0.149 sec)

    +-------------------+-----------+----------+---------------------------------------------------

    -+

    | Table | Op | Msg_type | Msg_text

    |

    +-------------------+-----------+----------+---------------------------------------------------

    -+

    | shop.customers | histogram | status | Histogram statistics created for column

    'country'. |

    +-------------------+-----------+----------+---------------------------------------------------

    -+

    1 row in set (7.037 sec)

    +----------------+-----------+----------+---------------------------------------------------+

    | Table | Op | Msg_type | Msg_text |

    +----------------+-----------+----------+---------------------------------------------------+

    | shop.orders | histogram | status | Histogram statistics created for column 'status'. |

    +----------------+-----------+----------+---------------------------------------------------+

    1 row in set (24.485 sec)

    *************************** 1. row ***************************

    EXPLAIN: -> Aggregate: count(0) (cost=68613 rows=1)

    -> Nested loop inner join (cost=66056 rows=11102)

    -> Filter: (o.`status` = 'cancelled') (cost=50315 rows=24704)

    -> Table scan on o (cost=50315 rows=499223)

    -> Filter: (c.country = 'US') (cost=0.537 rows=0.449)

    -> Single-row index lookup on c using PRIMARY (id = o.customer_id) (cost=0.537

    rows=1)

    1 row in set (0.001 sec)

    Output of Listing 3.112 Guessing 10% American customers (really 45%) and 25% cancelled orders (really 5%), the optimizer drove from customers and probed orders 44,938 times: 0.75 s, up to 2.6 s cold. STRAIGHT_JOIN forces the written order, and scanning orders first took 0.15 s. With histograms the estimates come within 1% (24,704 against 24,736) and the optimizer drives from orders unaided; the country histogram alone flipped the plan only in some runs.A histogram has up to 1,024 buckets (default 100), singleton as here or equi-height; beyond histogram_generation_max_mem_size (20 MB) it samples (72% of status). It refreshes only when rerun or with AUTO UPDATE, is ignored where an index can answer, and appears in information_schema.COLUMN_STATISTICS. Fix statistics before hard-wiring an order that cannot adapt.

    3.12.8 Optimizer Hints and Index Hints

    An optimizer hint, a /*+ ... */ comment after the first keyword, overrides one decision for one statement, at query-block (JOIN_ORDER, SEMIJOIN), table (MERGE, BNL) or index level (INDEX, NO_INDEX); SET_VAR sets a variable and MAX_EXECUTION_TIME caps a SELECT.

    SELECT /*+ MAX_EXECUTION_TIME(200) */ COUNT(*) FROM order_items WHERE quantity * unit_price > 90;

    SELECT /*+ INDEX(orders customer_id) */ COUNT(*) INTO @n FROM orders

    WHERE customer_id BETWEEN 1000 AND 30000 AND status = 'paid';

    SELECT /*+ NO_INDX(orders) */ COUNT(*) INTO @n FROM orders WHERE id < 10;

    EXPLAIN SELECT /*+ NO_MERGE(dt) NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM

    (SELECT id, customer_id FROM orders WHERE status = 'pending') AS dt WHERE dt.customer_id = 42\G

    Listing 3.113: A time cap, a forced index, a misspelled hint, and an unmerged derived table

    ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

    Query OK, 1 row affected (0.381 sec)

    Query OK, 1 row affected, 1 warning (0.000 sec)

    Warning (Code 1064): Optimizer hint syntax error near 'NO_INDX(orders) */ COUNT(*) INTO @n

    FROM orders WHERE id < 10' at line 1

    *************************** 1. row ***************************

    EXPLAIN: -> Index lookup on dt using <auto_key0> (customer_id = 42) (cost=61699..61702

    rows=10)

    -> Materialize (cost=61698..61698 rows=49891)

    -> Filter: (orders.`status` = 'pending') (cost=50203 rows=49891)

    -> Table scan on orders (cost=50203 rows=499223)

    1 row in set (0.000 sec)

    Output of Listing 3.113 Forcing customer_id onto the wide range of Section 3.12.1 took 0.38 s against 0.10 s for the optimizer's scan, and a misspelled hint is only a warning. The last plan shows what derived-table merging saves: unhinted, the subquery merges into the outer query and becomes an index lookup (0.06 ms), and under NO_MERGE alone, condition pushdown still copies the filter inside. With both off, all pending orders are materialized and indexed (<auto_key0>) first: 167 ms. The older index hints after a table name (USE, IGNORE, FORCE INDEX) are superseded by index-level hints, and the manual says to expect their deprecation. Comment why every hint exists.

    3.12.9 Sargable Predicates and Query Rewriting

    A predicate is sargable when the bare column meets a value computed once, so an index can seek. A function, arithmetic or type conversion on the column forces per-row evaluation: move the work to the constant side (price > 100 / 1.1, a date range, not YEAR(ordered_at) = 2026) or index the expression (Section 3.4.8). The slow query log proves a rewrite; it needs SET GLOBAL, so this ran on a private MySQL 9.7.2 container on the same machine, restarted cold:

    q="SELECT c.country, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o

    JOIN customers AS c ON c.id = o.customer_id JOIN order_items AS oi ON oi.order_id = o.id

    WHERE o.status <> 'cancelled' AND"

    g="GROUP BY c.country ORDER BY revenue DESC"

    sudo mysql shop -e "SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log',

    slow_query_log = ON, long_query_time = 0.05"

    sudo mysql shop -e "$q DATE_FORMAT(o.ordered_at, '%Y-%m') = '2026-08' $g" > /dev/null

    sudo mysql shop -e "ALTER TABLE orders ADD INDEX idx_ordered_at (ordered_at)"

    sudo mysql shop -e "$q DATE_FORMAT(o.ordered_at, '%Y-%m') = '2026-08' $g" > /dev/null

    sudo mysql shop -e "$q o.ordered_at >= '2026-08-01' AND o.ordered_at < '2026-09-01' $g" > /dev/null

    sudo grep '^# Query_time' /var/lib/mysql/slow.log

    Listing 3.114: Rewriting a monthly report step by step, measured by the slow query log

    # Query_time: 0.454882 Lock_time: 0.000036 Rows_sent: 10 Rows_examined: 551687

    # Query_time: 0.235647 Lock_time: 0.000004 Rows_sent: 10 Rows_examined: 551687

    # Query_time: 0.119962 Lock_time: 0.000002 Rows_sent: 10 Rows_examined: 67256

    Output of Listing 3.114 The report covers 14,811 orders, yet the first version examined 551,687 rows. The index alone changed only the cache temperature: DATE_FORMAT() hides the column and its selectivity (the optimizer expected about 370,000 matches), and in an earlier run it even switched to driving from customers (1.34 s). The sargable range examined 67,256 rows in 0.12 s. The loop is always the same: find the statement in the slow log (Section 3.13.8), EXPLAIN ANALYZE it, find where actual rows explode, change one thing, measure.

    3.12.10 Keyset Pagination and Other Anti-Pattern Fixes

    OFFSET 450000 reads and discards 450,000 rows, so every page is slower than the last. Keyset pagination remembers the previous page's last key and seeks past it:

    xa() { sudo mysql shop -Nre "EXPLAIN ANALYZE $1" |

    sed -E 's/^( *)(.*[^ ]) +(\(.*)$/\1\2\n\1 \3/'; }

    xa "SELECT id, status FROM orders ORDER BY id LIMIT 20 OFFSET 450000"

    xa "SELECT id, status FROM orders WHERE id > 450000 ORDER BY id LIMIT 20"

    Listing 3.115: Page 22,501 of the order list by OFFSET and by keyset

    -> Limit/Offset: 20/450000 row(s)

    (cost=40925 rows=20) (actual time=251..251 rows=20 loops=1)

    -> Index scan on orders using PRIMARY

    (cost=40925 rows=450020) (actual time=5.53..234 rows=450020 loops=1)

    -> Limit: 20 row(s)

    (cost=21326 rows=20) (actual time=5.69..5.71 rows=20 loops=1)

    -> Filter: (orders.id > 450000)

    (cost=21326 rows=106490) (actual time=5.69..5.7 rows=20 loops=1)

    -> Index range scan on orders using PRIMARY over (450000 < id)

    (cost=21326 rows=106490) (actual time=5.68..5.69 rows=20 loops=1)

    Output of Listing 3.115 The seek read 20 rows instead of 450,020, the same on every page. Sort on a unique key (append the primary key to a non-unique column) and give up jumping to page N. Spell the tie-breaker out: in the container of Section 3.12.9, ordered_at > ? OR (ordered_at = ? AND id > ?) was a range scan taking 0.37 ms, while (ordered_at, id) > (?, ?) walked 474,401 index entries in 175 ms.Other recurring fixes: replace N+1 loops with one join or IN batch (Section 4.16, eager loading in Chapter 5); name columns instead of SELECT * so a covering index can serve (Section 3.11.5); join a temporary table instead of a huge IN list; test existence with EXISTS, not COUNT(*) > 0; match literal types to columns (Section 3.3.9); and keep leading wildcards out of LIKE.