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.
