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'

-> 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

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 (Access Methods), 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.