EXPLAIN ANALYZE

EXPLAIN ANALYZE

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:

Estimates beside measurements, as a tree and as JSONSQL

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

Output

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

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 (Join Order). 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 (Indexing JSON).

MENU