Optimizer tracing: Query Execution Plan descriptions beyond EXPLAIN

Understanding why MySQL chooses a particular join order or why table scan is chosen instead of range scan is often very hard even for experienced MySQL users. Two almost identical queries, differing only in constant values, may produce completely different plans. That's why we're introducing a great new feature in 5.6: Optimizer Tracing. The target users of this feature are developers and MySQL users experienced enough to understand the ins and outs of EXPLAIN.

What Optimizer Tracing is
You may already have guessed this, but optimizer tracing is a printout  of important decisions the MySQL optimizer has done during the process of making the Query Execution Plan.

The trace is presented in JSON format which is easy to read both for humans and others.

Currently, the optimizer trace includes short explanations for:

• Why the join order was chosen.
• Important query transformations like IN to EXISTS.
• The access methods applicable and why one of them was chosen.
• Which conditions are attached to each table (i.e., what hides behind "Using where" in EXPLAIN)

More coverage will be added as we go along.


What Optimizer Tracing is NOT
The feature is not, and never will be, complete in the sense that it does not describe all choices the optimizer does. However, we're going to add more information when we find valuable things to add. We will not guarantee backwards compatibility like we do for EXPLAIN. This gives us the freedom to improve tracing at a high pace without going through multi-release deprecation warnings etc. In other words: optimizer tracing is not a replacement for EXPLAIN.

A quick tour
The best way get a feeling of optimizer tracing is to give it a try. Below is a teaser:

mysql> EXPLAIN SELECT * FROM orders WHERE  customer_id <> 6 OR (customer_id <> 8 AND value = 500);
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ALL  | customer_id   | NULL | NULL    | NULL |  160 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> /* Insert a few rows into orders */
mysql> EXPLAIN SELECT * FROM orders WHERE  customer_id <> 6 OR (customer_id <> 8 AND value = 500);
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | range | customer_id   | customer_id | 10      | NULL |   26 | Using index condition |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

So why did the plan change from table scan to range scan after inserting a few rows? Let's see what optimizer tracing has to report. You can turn on and modify it's behavior using a session variable called optimizer_trace. enabled=on turns on tracing, end_marker=off makes the trace a bit less human readable but JSON compliant and one_line=off is to make a pretty-printed trace:

mysql> SET optimizer_trace="enabled=on,end_marker=off,one_line=off";
mysql> SELECT * FROM orders WHERE  customer_id <> 6 OR (customer_id <> 8 AND value = 500);
mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE INTO DUMPFILE 'trace.json';

The performance of that query (and later queries in that session) will probably be bad, so be sure to turn off the tracing (enabled=off) once you've traced the queries you're interested in.

This is what it looks like when I open trace.json in Firefox using the JSONView add-on. I have collapsed ({...})a few objects that are not of interest in the example:

Some key information you can read from the trace:

• table scan would, if chosen, read an estimated 170 records at a cost of 38.64. 
• it's not possible to do range scan using the primary key, but the customer_id index may be used for this purpose.
• range scan (cost 34.21) is cheaper than table scan (cost 38.64), so MySQL choses to use range scan.

BTW: The example query was used because the range optimizer used to have a bug that resulted in incorrect ranges for the query. It used to be

"NULL < customer_id < 6", 
"6 <= customer_id <= 6 AND 500 <= value <= 500", 
"6 < customer_id < 8", 
"8 <= customer_id <= 8 AND 500 <= value <= 500", 
"8 < customer_id". 

This was the first bug optimizer tracing helped me fix.

If you want to know more:

• Guilhem Bichot has a blog about how to use the optimizer tracing related session variables.
• The internals manual has a chapter about it