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.…
Category Archives: Optimizer
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.
Better Performance for JOINs Not Using Indexes
In some cases it is not possible to use an index to optimize a JOIN. This may for example happen when you query the Performance Schema. As a result these kind of queries can be very slow; however in MySQL 5.6 and later you can use a trick to improve the performance considerably.…
A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements
In MySQL, query optimization of single-table UPDATE/DELETE statements is more limited than for SELECT statements. I guess the main reason for this is to limit the optimizer overhead for very simple statements. However, this also means that optimization opportunities are sometimes missed for more complex UPDATE/DELETE statements.…
Re-factoring some internals of prepared statements in 5.7
When the MySQL server receives a SELECT query, the query goes through several consecutive phases:
- parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
- resolution: the output of the parsing stage contains names of columns and names of tables.