InMotion Hosting Support Center

MySQL versus MariaDB

The Back Story of MySQL and MariaDB

MySQL and MariaDB share some common history, both are named after developer Michael Widenius' daughters, My and Maria. MySQL was created in 1995 by a Swedish software company, MySQL AB. It grew in popularity over the years and became the standard for the open-source relational database management system. In 2008, Sun purchased MySQL AB (and ultimately MySQL) for $1 billion. Not long after Sun was purchased by Oracle in April of 2009. At this point, Oracle a competing database system, became the owners of MySQL. This bothered the original developers seeing how they felt the future of MySQL was in jeopardy. Following the rules of open source, they decided to "fork" MySQL and create MariaDB. And there you have it, MySQL and MariaDB share a history!

MySQL and MariaDB Replication Versioning

Slave↓ Master→ MariaDB-5.5 MariaDB-10.0 MariaDB-10.1 MariaDB-10.2 MySQL-5.6 MySQL-5.7 MySQL-8.0
MariaDB-5.5 Ok No No No No No No
MariaDB-10.0 Ok Ok Ok
MariaDB-10.1 Ok Ok Ok Ok
MariaDB-10.2 Ok Ok Ok Ok Ok Ok

Feature Comparison of MySQL and MariaDB

More Storage Engines

MariaDB contains the standard storage engines MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE. It also includes some of the less common storage engines such as;

  • Cassandra (MariaDB 10.0)
  • Spider (MariaDB 10.0+)
  • SEQUENCE (MariaDB 10.0+)
  • CONNECT (MariaDB 10.0+)
  • TokuDB (MariaDB 5.5+)
  • SphinxSE (MariaDB 5.2+)
  • OQGRAPH (Not Available in MariaDB 5.5)
  • FederatedX (Drop in replacement for Federated)
  • Aria (MyISAM replacement with improved caching)
  • MyRocks (MariaDB 10.2 storage engine with enhanced compression)
  • ColumnStore (a column oriented storage engine optimized for data warehousing)

Speed Improvements

MariaDB has many improved enhancements over the MySQL when comparing the Optimizer feature:

Feature MariaDB 5.3/5.5 MariaDB 10.0 MySQL 5.5 MySQL 5.6
Disk access optimizations
Index Condition Pushdown (ICP) YES YES - YES
Disk-sweep Multi-range read (DS-MRR) YES YES - YES
DS-MRR with Key-ordered retrieval YES YES - -
Index_merge / Sort_intersection YES YES - -
Cost-based choice of range vs. index_merge YES YES - -
ORDER BY ... LIMIT <small_limit> - YES - YES
Use extended (hidden) primary keys for innodb/xtradb YES (5.5) YES - -
Join optimizations
Batched key access (BKA) YES YES - YES
Block hash join YES YES - -
User-set memory limits on all join buffers YES YES - -
Apply early outer table ON conditions YES YES - -
Null-rejecting conditions tested early for NULLs YES YES - -
Subquery optimizations
In-to-exists YES YES YES YES
Semi-join YES YES - YES
Materialization YES YES - YES
NULL-aware Materialization YES YES - -
Cost choice of materialization vs. in-to-exists YES YES - YES
Subquery cache YES YES - -
Fast explain with subqueries YES YES - -
EXISTS-to-IN - YES - -
Optimization for derived tables / views
Delayed materialization of derived tables / materialized views YES YES - YES
Instant EXPLAIN for derived tables YES YES - YES
Derived Table with Keys optimization YES YES - YES
Fields of merge-able views and derived tables used in equality optimizations YES YES - -
Execution control
LIMIT ROWS EXAMINED rows_limit YES (5.5) YES - -
Optimizer control (optimizer switch)
Systematic control of all optimizer strategies YES YES - partial
EXPLAIN improvements
Explain for DELETE, INSERT, REPLACE, and UPDATE - Partial - YES
EXPLAIN in JSON format - - - YES
More detailed and consistent EXPLAIN for subqueries YES YES - -
Optimizer trace
Optimizer trace - - - YES

  • Parallel replication — new in 10.0; takes place in three parts: replication events are read by the IO thread and queued in the relay log, fetched individually by the SQL thread from the relay log, and each event is applied on the slave replicating the changes on the master.
  • Performance improvements include a better InnoDB asynchronous IO subsystem on Windows.
  • Indexes for the MEMORY(HEAP) engine are quicker. The latest fixes were applied in MariaDB 5.5 and MySQL 5.7.
  • Segmented Key Cache for MyISAM was added in MariaDB 5.2. This has improved performance in MyISAM tables up to 4x.
  • As of version 10.0.13, the adjustable hash size for MyISAM and Aria improves shutdown time if you are using a lot of MyISAM/Aria tables with delayed keys.
  • With the CHECKSUM TABLE using the QUICK option speed is improved.
  • Performance was improved with the character set conversions and removal of conversions(when they were not needed). Overall speed improvement is about 1-5% but can be faster with big result sets.
  • Pool of Threads in MariaDB 5.1/MariaDB 5.5 allows MariaDB to run with 200,000+ connections leading to speed improvement when using many connections.
  • Client connection improvements added as of MariaDB 10.1 and MariaDB 10.2.
  • Some improvements to the DBUG code within MariaDB help make the code run faster with the debug compiled in, but not used.
  • Using the Aria storage engine using internal temporary tables allows for improved performance.
  • The test suite runs faster even with the extended test list.

New Features and Extensions

Better Testing

MariaDB contains more tests in the test suite than MySQL. Invalid and any unnecessary tests have been removed. Issues with the test suite have been fixed.

Fewer Bugs

Currently, MariaDB community is alert and works to fix bugs as quickly and as thoroughly as possible. Additionally, compiler warnings have also been reduced as a result of the reduction in bugs.

Open Source

All the source code for MariaDB is released under GPL, LGPL, or BSD. While MySQL has closed source modules in their Enterprise Edition, MariaDB does not have any closed source modules. MariaDB included all the closed source features contained in MySQL 5.5 Enterprise Edition in their open source version. MariaDB includes test cases for all new fixed bugs. MySQL does not provide test cases for bugs fixed in MySQL 5.5. MariaDB makes all future development plans public, including bugs and their fixes. MariaDB has a large community and that community of developers includes a wide variety of contributors whereas all commits mafe to MySQL appear to be from Oracle employees. MySQL client libraries are released under the GPL license which prohibits linking to closed source applications. On the contrary, MariaDB licenses client libraries under the LGPL license allowing linking to closed source software.

Compatibility Issues between MariaDB & MySQL

MariaDB was designed to be a drop-in replacement for MySQL and was a fork of the original MySQL code base. This means that when switching from MySQL to MariaDB it's a relatively straightforward process. You simply uninstall MySQL and install MariaDB. Since it is a drop-in replacement there's no need to convert any data. MariaDB's developers perform a monthly merge of the MySQL code to ensure they remain compatible. There are different incompatibilities between versions of MySQL and MariaDB even though versions are designed to be compatible by their respective version numbers (for example MySQL 5.1 -> MariaDB 5.1 & MySQL 5.5 -> MariaDB 5.5).

Incompatibilities between MariaDB 10.0 & MariaDB 5.5 / MySQL 5.5

  • SET OPTION syntax is deprecated in MariaDB 10.0 and MySQL 5.6. Use just SET.

Incompatibilities between MariaDB 10.0 & MySQL 5.6

  • All MySQL binaries (MySQLd, myisamchk etc.) give a warning if one uses a prefix of an option (such as --big-table instead of --big-tables). MariaDB binaries work in the same way as most other Unix commands and don't give warnings when using unique prefixes.
  • MariaDB GTID is not compatible with MySQL 5.6. This means that one can't have MySQL 5.6 as a slave for MariaDB 10.0. However MariaDB 10.0 can be a slave of MySQL 5.6 or any earlier MySQL/MariaDB version.
  • MariaDB 10.0 multi-source replication is not supported in MySQL 5.6.
  • MariaDB 10.0 dynamic columns are not supported by MySQL 5.6.
  • To make CREATE TABLE ... SELECT work the same way in statement based and row based replication it's by default executed as CREATE OR REPLACE TABLE on the slave. One benefit of this is that if the slave dies in the middle of CREATE ... SELECT it will be able to continue.
  • One can use the slave-ddl-exec-mode variable to specify how CREATE TABLE and DROP TABLE is replicated.
  • See also a detailed breakdown of System variable differences between MariaDB 10.0 and MySQL 5.6.
  • MySQL 5.6 has performance schema enabled by default. For performance reasons MariaDB 10.0 has it disabled by default. You can enable it by starting MySQLd with the option --performance-schema.
  • MariaDB 10.0 does not support the MySQL Memcached plugin.
  • Users created with MySQL's SHA256 password algorithm cannot be used in MariaDB 10.0.
  • MariaDB 10.0 does not support delayed replication - MDEV-7145.

Incompatibilities between MariaDB 10.1 & MySQL 5.7

  • MariaDB 10.1 does not support MySQL 5.7's JSON.
  • MariaDB 10.1's InnoDB encryption is implemented differently than MySQL 5.7's InnoDB encryption.
  • MariaDB 10.1 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.
  • MariaDB 10.1 does not support multiple triggers for a table - MDEV-6112.
  • MariaDB 10.1 does not support CREATE TABLESPACE for InnoDB.

Incompatibilities between MariaDB 10.2 & MySQL 5.7

  • System variable differences between MariaDB 10.2 and MySQL 5.7.
  • Function differences between MariaDB 10.2 and MySQL 5.7.
  • Multiple triggers per table was added in 10.2
  • MariaDB InnoDB encryption is implemented differently than MySQL 5.7's InnoDB encryption.
  • MariaDB stores JSON as true text, not in binary format as MySQL. The reason is that our JSON functions are much faster than MySQL's so we didn't need to see a need for storing things in binary format as it adds a lot of complexity when manipulating JSON objects.
  • MariaDB 10.2 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.
  • MariaDB 10.2 does not support the MySQL X plugin.
  • MariaDB 10.2 does not support the MySQL general table spaces.
  • Also see Incompatibilities between MariaDB 10.1 and MySQL 5.7.

Support Center Login

Our Login page has moved, Click the button below to be taken to the login page.

Social Media Login

   
Social Login Joomla

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

0 Questions & Comments

Post a comment

Back to first comment | top

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!