Let's look at the main improvements in the MySQL 8.0 optimizer:
- MySQL 8.0 now more effectively supports advanced SQL features such as common table expressions, windowing functions, and the grouping() function
- One feature that DBAs will appreciate is the invisible index
- More tips in Query Rewrite Plugin
- On the performance side, the cost model has really been improved for superior performance
- Histogram statistics in MySQL 8.0 improve the statistics based on data distribution
I've always mentioned that to get optimal performance, you need to understand how MySQL thinks or reacts to your queries; this step in understanding is much more important than you think. For example, have you ever had situations where a complex query did not use the index that it had to use?
Let's have a look at how the high-level optimizer responds...