Skip to content

MySQL Optimizer Trace to debug slow queries

An optimizer trace can show why a query executes slowly in MySQL. The trace contains costs of different indexes that are evaluated and reasons why one is chosen over the other.

Get a trace

Traces are per session. Enabling optimizer_trace doesn't affect other sessions.

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
# Run query
SELECT ...;
# See the trace of the query
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

Read a trace

Optimizer traces are JSON encoded. Best to copy the structure in a JSON viewer. The trace contains these steps:

  • join_preparation shows early query rewrites
  • join_optimization shows how execution plan is built
    • condition_processing basic rewrites in WHERE/ON conditions
    • ref_optimizer_key_uses: Construction of possible ways to do ref and eq_ref accesses
    • rows_estimation: Estimations of index costs, usable and cause can give hints why an index is not used
    • considered_execution_plans: Choice of the join order
    • attaching_conditions_to_tables Once the join order is fixed, parts of the WHERE clause are "attached" to tables to filter out rows as early as possible
  • join_execution shows any optimizations during execution. The optimizer can overwrite an execution plan with heuristics e.g. reconsidering_access_paths_for_index_ordering.

Sources

  • MySQL docs
  • MariaDB docs

#published

Links to this note

These notes are unpolished collections of thoughts, unfinished ideas, and things I want to remember later. In the spirit of learning in public, I'm sharing them here. Have fun exploring, if you want!
© 2020 by Adrian Philipp