In a recent blog post my colleague Roy presented his work for MySQL 5.7 that makes the query optimizer merge sub-queries in the FROM clause (so-called derived tables) with the outer query. I will in this blog post show an example of how this work improves the performance of a MySQL query.…
Category Archives: Optimizer
Derived Tables in MySQL 5.7
In MySQL 5.7, we have enhanced the optimizer so that derived tables and views are handled more consistently.
Just to recap: A derived table is a subquery that can take the place of a table in the FROM clause of an SQL statement.…
When ONLY_FULL_GROUP_BY Won’t See the Query Is Deterministic…
This is a follow-up post to my recent announcement of only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).
In the SQL standard, GROUP BY should contain only table columns and never expressions.…
Some Notes on Index Statistics in InnoDB
In MySQL 5.6 we introduced a huge improvement in the way that index and table statistics are gathered by InnoDB and subsequently used by the Optimizer during query optimization: Persistent Statistics. Some aspects of the way that Persistent Statistics work could be improved further though, and we’d really like your input on that.…
MySQL 5.7: only_full_group_by Improved, Recognizing Functional Dependencies, Enabled by Default!
Here’s an account of recent work which has kept me busy and excited for a few months. For those unfamiliar with the only_full_group_by sql mode, let me provide some context. I’ll use the world database, which can be downloaded from this MySQL Documentation page.…
Write Yourself a Query Rewrite Plugin: Part 2
In my last post I covered how to use the query rewrite framework to write your own pre-parse plugin. The interface is simplistic: a string goes in, a string comes out, and the rest is up to your plugin’s internal workings.…
Write Yourself a Query Rewrite Plugin: Part 1
With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.…
Generated Columns in MySQL 5.7.5
Generated Columns is a new feature available in the latest lab release. This work is based on a contribution by Andrey Zhakov. Thanks, Andrey! The Optimizer team modified it to follow the current MySQL design, and to lift a number of limitations.…
The Query Rewrite Plugins
Why Query Rewrites?
Now that the cost model project is progressing, most of you are going to notice execution plan changes. In the vast majority of the cases, the changes will be for the better, and some bugs with a long history will finally be closed.…
Optimizer Cost Model Improvements in MySQL 5.7.5 DMR
In a previous blog post we presented some of the issues with the current optimizer cost model and listed several ideas for improvements. The new 5.7.5 DMR contains the result of our initial work on improving the optimizer’s cost model:
- Cost Model for WHERE Conditions.