Lesson 23 / 28

EXPLAIN at a Glance

See how MySQL plans to run a query.

Reading a query plan

EXPLAIN shows whether MySQL will use an index or scan the whole table.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

Output:

id | table  | type | possible_keys        | key                  | rows
1  | orders | ref  | idx_orders_customer  | idx_orders_customer  | 2

type: ALL is a red flag

type: ALL in EXPLAIN means a full table scan — on a large table, that's usually a sign a useful index is missing.

Quick check: In EXPLAIN output, what does `type: ALL` usually indicate?

  • The query used an index perfectly
  • A full table scan happened
  • The table has no rows
Answer

A full table scan happened — ALL means MySQL had to read every row — often fixable with an index.