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