The Ultimate Guide to High Availability with MariaDB

What is the difference between MySQL and MariaDB query optimizers?

You are viewing an old version of this article. View the current version here.

This article is about query optimizer feature development in recent versions of MySQL and MariaDB.

To make things easier to understand, let's first partition the optimizer into areas:

optimizer-areas-r2

A bit of history will also help. There is no written evidence of use of SQL query optimization by ancient Egyptians. In April 2012, MariaDB 5.3 and MariaDB 5.5 became stable. They included a number of major query optimizer features:

optimizer-areas-mariadb-55-r2

The size of the bubble roughly corresponds to the impact of the feature (in opinion of the author of this article). "Hash join" is in quotes, because this feature is not what is generally meant by hash join in the world of databases. Smaller features and bugfixes are not shown.

In Feburary, 2013, MySQL 5.6 has its first stable release. It also had a number of major optimizer features. Some of them had common ancestry with MariaDB's features, and some of them were new development:

optimizer-areas-mysql-56-r2

In April, 2014, MariaDB 10.0 became stable. As compared to MariaDB 5.5, it had some features that were backported from MySQL 5.6, and also some new features were implemented:

optimizer-areas-mariadb-100-r2

One can see that

  • MariaDB has features that MySQL doesn't have
  • However, some of features of MySQL 5.6 were not ported into MariaDB 10.0.

MariaDB team is frequently asked if all features (or feature X) from MySQL 5.6 are available in MariaDB, so here's our position on the features that have not [yet] been backported:

  • EXPLAIN FORMAT=JSON - we intend to port it in the next MariaDB release. We will likely fix the gaps we've found in MySQL's implementation and try to make it more user-friendly.
  • Optimizer trace - we have doubts whether the feature is usable in its current form. For simple queries, it's easier to analyze using other means, for complex queries the size of the trace grows beyond any limit. We're looking for input re how optimizer trace was (or could be) useful to somebody in a real-world setting.
  • Faster optimization for eq_ref - we believe that the optimization is useful but its implementation in MySQL uses a wrong approach.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.