Over a million developers have joined DZone.

Guidance for MySQL Optimizer Developers

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.

From http://www.mysqlperformanceblog.com/

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}