The MySQL Query Optimizer Explained Through Optimizer Trace

 May 29, 2019
 Percona Live Open Source Database Conference 2019
  Øystein Grøvlen

All major considerations and decisions made by the MySQL Query Optimizer may be recorded in an Optimizer Trace. While EXPLAIN will show the query execution plan the Query Optimizer has decided to use, MySQL's Optimizer Trace will tell WHY the optimizer selected this plan.

This presentation will introduce you to the inner workings of the MySQL Query Optimizer by showing you examples with Optimizer Trace. We will cover the main phases of the MySQL optimizer and its optimization strategies, including query transformations, data access strategies, the range optimizer, the join optimizer, and subquery optimization. We will also show how optimizer trace gives you insight into the cost model and how the optimizer does cost estimations.

By attending this presentation you will learn how you can use information from Optimizer Trace to write better-performing queries. The presentation will also cover tools that can be used to help process the vast amount of information in an optimizer trace.

About the Author

Øystein Grøvlen

Øystein Grøvlen is a Senior Staff Engineer in Alibaba Cloud, where he works in the Database Products Group. Before joining Alibaba, Øystein worked for 10 years in the MySQL optimizer team at Sun/Oracle. At Sun Microsystems, he was also a contributor on the Apache Derby project and Sun's Architectural Lead on Java DB. Prior to that, he worked for 10 years on development of Clustra, a highly available DBMS. Øystein has been talking about the MySQL Optimizer at several conferences over the years. Dr. Grøvlen has a PhD in Computer Science from the Norwegian University of Science and Technology. Øystein is a native Norwegian, but currently lives in Hangzhou, China.