The Extra Column

The Extra Column

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

Five plans, five kinds of ExtraSQL

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'

Output

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)

Using index: the index covers the query (Covering Indexes), 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, Temp Tables and Sorts). 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 (Nested-Loop and Hash Joins).

Others include Backward index scan, Impossible WHERE, and the semijoin strategies FirstMatch and LooseScan (IN, ANY, ALL, and EXISTS).

MENU