Analyzing MySQL 5.7 Optimizer Trace: Why a Suboptimal Execution Plan Occurs and How to Use Hints

The article starts with a slow SQL statement from the "26 Questions" series that runs poorly on MySQL 5.7, prompting the authors to add a hint after observing an inefficient execution plan.

Using the optimizer_trace tool, they increase the trace memory, enable tracing, and retrieve the full trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE , which is a large JSON document.

By visualizing the JSON, they identify six steps in the optimization process: temporary table creation, join preparation, two join‑optimizations, and join execution. The key observation is that the optimizer chose an EXISTS subquery instead of materializing the subquery, which they suspect is due to a mis‑estimated cost.

The cost model shows that the optimizer calculates the cost of an EXISTS subquery as the cost of executing the subquery once multiplied by the number of times it must be executed. The number of executions equals the fan‑out (row count) of the outer query.

For the information_schema.columns table, the fan‑out is reported as zero because MySQL treats metadata tables specially and does not provide meaningful row‑count estimates. This zero fan‑out leads the optimizer to abandon materialization and select the less efficient plan.

The root cause is therefore MySQL 5.7’s distinct handling of metadata tables in information_schema , which differs from ordinary tables.

To avoid similar issues, the authors recommend using hints to guide the optimizer when querying metadata tables in MySQL 5.7, and note that MySQL 8.0 rewrites most metadata tables as views backed by hidden tables in the mysql schema, resulting in more reasonable execution plans.

Code example for exporting the trace to a file:

SELECT TRACE INTO DUMPFILE <filename> FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;