ANALYZE

Runs a SELECT with statistics collection turned on and reports what happened: each step’s access method, the rows examined, and the pages read. It reports the run that actually took place, not an estimate.

Syntax

ANALYZE <select statement>

Example

ANALYZE SELECT * FROM passengers WHERE id = 1;

analyze rows=1 class=PointSelect steps=1 examined=1 pages=1 opens=1 pattern_id=0x40a5b5e4e01959eb fetch_id=0x9b8e7707127bd8d4
class=PointSelect steps=1 hoisted=0
step 0 Lookup passengers key=1
  filter 0:0.0 = 1
project * (4 column(s) of step 0)
step 0 Lookup passengers opens=1 examined=1 matched=1 sel=100% pages=1

Output

Field	Meaning
rows	Rows returned.
class	The statement’s execution class, decided when it was parsed, such as PointSelect.
steps	Number of steps in the plan. One step per table access.
examined	Rows the engine looked at. For a sorted LIMIT, this is every matching row.
pages	Pages read.
pattern_id	Identifies the statement’s shape. Statements that differ only in literal values share it, and its plan never changes.
fetch_id	The shape without the select list; the identifier listed by SHOW PATTERNS.

Further lines describe each step: its access method (key lookup, range, index, Cabin, scan), whether a sort ran, and whether a join built an in-memory lookup table for its inner side (build, inner_built).

Remarks

• ANALYZE uses the same parse, plan and fingerprint as the plain statement.
• An ORDER BY on the primary key ascending shows no sort step, because none is needed.

Limitations

• SELECT only. A write statement cannot be analyzed.

See also

• SELECT
• SHOW and DESCRIBE