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:

Tips and tricks: Killer response time for non-overlapping intervals

Assume you have a table where you store non-overlapping intervals using two columns, e.g. IP ranges. IP ranges are simple to represent using integer notation:

CREATE TABLE ip_owner (

   owner_id int NOT NULL,
   /* some columns */

   ip_start_int bigint NOT NULL,      /* IP address converted to integer */
   ip_end_int bigint NOT NULL,        /* IP address converted to integer */
   PRIMARY KEY (owner_id),

   INDEX ip_range (ip_start_int, ip_end_int)

) ENGINE=InnoDB;

And then you find yourself in a situation where you want to know who, if anyone, owns the IP address X. This can be done using the following query:

SELECT * FROM ip_owner WHERE ip_start_int <= X AND ip_end_int >= X;

MySQL can resolve this using a range scan, but will unfortunately only be able to use the ip_start_int <= X part of the condition as a range as explained here. Thus, the query will either be resolved by range scan if fairly few records have ip_start_int <= X or table scan otherwise. That means unreliable response time because it will be much quicker to query low-valued IPs than high valued IPs. I inserted 1M records into the table before running the queries below:

The MySQL range access method explained

The range access method uses an index to read a subset of rows that form one or multiple continuous index value intervals. The intervals are defined by the query's range predicates, which are comparisons using any of =, <=>, IN(), IS NULL, IS NOT NULL, >, <, >=, <=, BETWEEN, !=, <> or LIKE.

Some examples:

SELECT * FROM blog WHERE author_id IN (1, 7, 8, 10)

SELECT * FROM orders WHERE value > 1000

You know that the range access method is used when EXPLAIN shows type=range.

Naturally, there has to be an index on the column used by the range predicate. Since indexes are ordered, MySQL will, for each interval, dive down the index using the interval start value and read it's way through the index leaves until it reaches the interval end value:

The meaning of ref=func in MySQL EXPLAIN

When EXPLAIN shows that a table is accessed using the [eq_]ref access type, we're used to look at the ref column to see where MySQL gets the value to look up from. Usually, we see either "const" if the value is provided as a constant in the query or a column name if the value is read from a column in an already read table:

EXPLAIN 
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber
FROM accounts_receivable as acc_rec
WHERE acc_rec.cust_id=1;
+----+-------------+---------+------+---------+-------+
| id | select_type | table   | type | key     | ref   |
+----+-------------+---------+------+---------+-------+
| 1  | SIMPLE      | acc_rec | ref  | cust_id | const |
+----+-------------+---------+------+---------+-------+

EXPLAIN 
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber,
       customers.firstname, customers.lastname, customers.phone
FROM accounts_receivable AS acc_rec JOIN customers AS cust
ON acc_rec.cust_id = customers.cust_id;
+----+-------------+---------+--------+---------+-----------------+
| id | select_type | table   | type   | key     | ref             |
+----+-------------+---------+--------+---------+-----------------+
| 1  | SIMPLE      | acc_rec | ALL    | NULL    | NULL            |
| 1  | SIMPLE      | cust    | eq_ref | PRIMARY | acc_rec.cust_id |
+----+-------------+---------+--------+---------+-----------------+

But what if ref shows the value "func"? In this case, the value used as input to [eq_]ref is the output of some function. A few examples: