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.
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 querySELECT ...;# See the trace of the querySELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;# When done with tracing, disable it:SET optimizer_trace="enabled=off";
Optimizer traces are JSON encoded. Best to copy the structure in a JSON viewer. The trace contains these steps:
join_preparationshows early query rewrites
join_optimizationshows how execution plan is built
condition_processingbasic 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,
causecan give hints why an index is not used
considered_execution_plans: Choice of the join order
attaching_conditions_to_tablesOnce the join order is fixed, parts of the WHERE clause are "attached" to tables to filter out rows as early as possible
join_executionshows any optimizations during execution. The optimizer can overwrite an execution plan with heuristics e.g.