Advanced query optimizer tuning and analysis

Developing Applications
22 April 1:30pm - 4:30pm @ Ballroom B

How can you tell if queries in your application use good query plans? And if they don't, is there any way to force the query optimizer to use a good query plan?

This tutorial aims to give an all-around answer. We will:

  1. show how to find queries which use expensive query plans,
  2. discuss possible reasons why the optimizer can make poor query plan choices,
  3. show how to determine what exactly went wrong for a given query, and
  4. show how to force the optimizer pick a good query plan.

The tutorial can be considered a sequel to the last year's optimizer tutorial. This year, we will assume is familiar with the basic topics like range and ref access and will focus on the optimization of complex SQL constructs like joins, ORDER/GROUP BY, and subqueries. The tutorial will cover the latest additions to the MySQL and MariaDB optimizers, including things like optimizer trace, PERFORMANCE_SCHEMA and SHOW EXPLAIN.

Speakers

Sergei Petrunia
Optimizer Developer, Monty Program Ab
Biography: 
Sergei Petrunia is a query optimizer developer at Monty Program Ab. He implemented MariaDB's optimizer features like Table Elimination (MariaDB 5.1), improvements to semi-join subquery optimizations (MariaDB 5.3/5.5) and SHOW EXPLAIN (MariaDB 10.0). Prior to Monty Program, he has worked for MySQL Ab/Sun Microsystems, where he was the author of query optimizer features like Partition Pruning, Index Merge (MySQL 5.1), and numerous smaller improvements in query optimizer and other parts of the server. He maintains a highly technical blog at http://s.petrunia.net/blog
Timour Katchaounov
Query Optimizer Developer, Monty Program AB
Biography: 
Timour has worked on the MySQL/MariaDB optimizer since 2004. Some of the projects he implemented are a greedy query optimizer and subquery materialization. He also worked on re-engineering and other optimizer projects. Before joining MySQL, he received a Ph.D. in database technology from Uppsala University, Sweden. During his studies he contributed code to the federated query optimizer of IBM's DB2.

Slides