While EXPLAIN shows the selected query plan for a query, optimizer trace will show you WHY the particular plan was selected. From the trace you will be able to see what alternative plans was considered, the estimated costs of different plans, and what decisions was made during query optimization.
To turn on recording of optimizer trace for the current session:
When optimizer trace is enabled, the information schema table optimizer_trace will contain the trace for the latest query that was explained or executed. I usually dump the trace to a file using this SQL statement:
One important thing to note is that there is a configurable maximum size for the memory buffer used to record the trace. The default is pretty low, and you will often have to increase the size to capture the entire trace. The missing_bytes_beyond_max_mem_size column of the optimizer_trace table shows how many bytes are missing from the trace. If this column is non-zero, you should increase the setting of the variable optimizer_trace_max_mem_size:
The above statement increases the trace buffer to about 1 MB. Unless your query joins very many tables, this should be sufficient.
To get more information about optimizer trace, check:
To turn on recording of optimizer trace for the current session:
SET optimizer_trace='enabled=on';
When optimizer trace is enabled, the information schema table optimizer_trace will contain the trace for the latest query that was explained or executed. I usually dump the trace to a file using this SQL statement:
SELECT trace FROM information_schema.optimizer_trace
INTO OUTFILE '<filename>' LINES TERMINATED BY '';
One important thing to note is that there is a configurable maximum size for the memory buffer used to record the trace. The default is pretty low, and you will often have to increase the size to capture the entire trace. The missing_bytes_beyond_max_mem_size column of the optimizer_trace table shows how many bytes are missing from the trace. If this column is non-zero, you should increase the setting of the variable optimizer_trace_max_mem_size:
SET optimizer_trace_max_mem_size=1000000;
The above statement increases the trace buffer to about 1 MB. Unless your query joins very many tables, this should be sufficient.
To get more information about optimizer trace, check:
