Wednesday, January 27, 2016

How to Get Optimizer Trace for a Query

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:
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:

Optimizer tracing: Query Execution Plan descriptions beyond EXPLAIN
Optimizer tracing: how to configure it
MySQL Internals Manual on Optimizer Tracing

Tuesday, January 26, 2016

Improved Performance of Queries with Derived Tables

Last month/year I published a blog post on mysqlserverteam.com with an example of how MySQL 5.7 gives you improved performance for queries with derived tables.

The query example was part of my tutorial “How to Analyze and Tune MySQL Queries for Better Performance” at Oracle OpenWorld 2015. Slides for the entire presentation can be found here.