Advanced query optimizer tuning and analysis
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:
- show how to find queries which use expensive query plans,
- discuss possible reasons why the optimizer can make poor query plan choices,
- show how to determine what exactly went wrong for a given query, and
- 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.