Guidance for MySQL Optimizer DevelopersPeter Zaitsev
I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last 10 years I chased first Monty and later Igor with Optimizer complains and suggestions. Here are some general ideas which I think can help to make optimizer in MySQL, MariaDB or Drizzle better.
Make it Plugable Every Major optimizer change causes a lot of pain for some of the users. Even if you improve performance 99 our of 100 queries there are going to be countless users complaining about the change. Due to this problem Optimizer Team was more conservative than I think they could have been. The solution is simple – make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version.
Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs/Seeks in same way for all data sets. In practice some people have their database 100% in RAM (even for Disk tables such MyISAM or Innodb) others keep database on SSD which has completely different ratio between CPU and IO cost.
Focus on Execution Methods Performance problems can be due to optimizer picking the wrong plan, such as doing full table scan when Index access is better or because MySQL simply does not have execution method to resolve query in optimal way – loose index scan, hash join, sort merge join are all the examples of such. For me it is most important to ensure MySQL has proper ways to execute the query. It may not always pick them right but at least it allows to get query going right manually.
Zero Administration Tunables and Hints Zero Administration is great. I would love to see Optimizer which always choses the fastest plan for the query (not the plan with lowest “cost” but the one which actually gives best performance). I also recognize there are always going to be cases when Optimizer will not pick the right plan. So I would like to see tuning knobs (which relates to cost model and various optimizations) as well as simply hints. Any way MySQL could possibly execute query should be possible to force with hints. In MySQL 4.0 this was the case, in recent versions number of optimizations have been added which can’t be easily forced with hints.
Another benefit of having ways to force any optimizer behavior with hints (rather than by changing the code) is the data Optimizer Team can get back from customers and community – it because very easy for users to show there is the plan which works better and so let the team know which cases are not handled best by the optimizer logic.