MySQL Internals Manual  /  Tracing the Optimizer  /  Selecting Optimizer Features to Trace

8.12 Selecting Optimizer Features to Trace

Some features in the optimizer can be invoked many times during statement optimization and execution, and thus can make the trace grow beyond reason. They are:

  • Greedy search: with a N-table join, this could explore factorial(N) plans

  • Range optimizer

  • Dynamic range optimization (known as "range checked for each record" in EXPLAIN output: each outer row causes a re-run of the range optimizer)

  • Subqueries: a subquery in WHERE clause may be executed once per row.

Those features can be excluded from tracing using the optimizer_trace_features system variable, which has these on/off switches:

  • greedy_search: if off, greedy search is not traced

  • range_optimizer: if off, range optimizer is not traced

  • dynamic_range: if off, only the first call to the range optimizer on this JOIN_TAB::SQL_SELECT is traced

  • repeated_subselect: if off, only the first execution of this Item_subselect is traced.