MySQL vs MariaDB: Performance & Feature Differences
Title: MariaDB versus MySQL - Features
Last updated: 7 May, 2019
MariaDB Server began its life as a fork of MySQL, inheriting the strong performance and the legacy Relational DBMS capabilities that made the ecosystem so popular in the first place. In the years since this fork, differences have begun to emerge between these platforms, as MariaDB has advanced as a leader in its own right. These differences translate into advantages, many of which are specific to MariaDB users, including enhancements to the optimizer, window functions, system and application-time period versioned tables, replication and clustering support, and support for new storage engines like MariaDB ColumnStore, MyRocks, Spider, and Aria.
For more information on compatibility, issues that many arise in migrating to MariaDB, and feature comparisons, see MariaDB vs MySQL - Compatibility
Differences per Releases
Due to the growing differences between them, users interested in migrating from MySQL to MariaDB Server should first consider compatibility issues that may arise from the change. Below is a list of pages specifically calling out incompatibilities between a release of MariaDB Server and the roughly contemporary release of MySQL.
- For differences between MySQL 5.7 and MariaDB 10.3 specifically, see Incompatibilities and Feature Differences Between MariaDB 10.3 and MySQL 5.7
- For differences between MySQL 5.7 and MariaDB 10.2 specifically, see Incompatibilities and Feature Differences Between MariaDB 10.2 and MySQL 5.7
In addition to general incompatibilites, succeeding releases can also differences in the default values set on system variables. In cases where your application or infrastructure anticipates a particular default value, this can lead to unexpected behavior.
- System variable differences between MariaDB 10.4 and MySQL 8.0
- System variable differences between MariaDB 10.3 and MySQL 8.0
- System variable differences between MariaDB 10.3 and MySQL 5.7
- System variable differences between MariaDB 10.2 and MySQL 5.7
- System variable differences between MariaDB 10.1 and MySQL 5.7
- System variable differences between MariaDB 10.1 and MySQL 5.6
- System variable differences between MariaDB 10.0 and MySQL 5.6
- System variable differences between MariaDB 5.5 and MySQL 5.5
Development of SQL functions follows different tracks for MariaDB and MySQL, to the point where each includes built-in functions that are not available in the other. For more information and comparison of the functions available in each, see the pages linked in the list below.
- Function Differences Between MariaDB 10.4 and MySQL 8.0
- Function Differences Between MariaDB 10.3 and MySQL 8.0
- Function Differences Between MariaDB 10.3 and MySQL 5.7
- Function Differences Between MariaDB 10.2 and MySQL 5.7
More Storage Engines
- ColumnStore, a column oriented storage engine optimized for Data warehousing.
- MyRocks, a storage engine with great compression, in 10.2
- Aria, MyISAM replacement with better caching.
- FederatedX (drop-in replacement for Federated)
- OQGRAPH (In MariaDB 5.2 and later. Disabled in MariaDB 5.5 only.) — new in 5.2
- SphinxSE — new in 5.2
- TokuDB in MariaDB 5.5 and later.
- CONNECT in MariaDB 10.0 and later.
- SEQUENCE in MariaDB 10.0 and later.
- Spider in MariaDB 10.0 and later.
- Cassandra in MariaDB 10.0
MariaDB Server includes a number of competitive performance improvements over MySQL, whcih include:
- Many optimizer enhancements in MariaDB 5.3. Subqueries are now finally usable. The complete list and a comparison with MySQL is here. A benchmark can be found here.
- Faster and safer replication: Group commit for the binary log. This makes many setups that use replication and lots of updates more than 2x times faster.
- Parallel replication — new in 10.0
- Improvements for Innodb asynchronous IO subsystem on Windows.
- Indexes for the MEMORY(HEAP) engine are faster. According to a simple test, 24% faster on INSERT for integer index and 60% faster for index on a CHAR(20) column. Fixed in MariaDB 5.5 and MySQL 5.7.
- Segmented Key Cache for MyISAM. Can speed up MyISAM tables with up to 4x — new in 5.2
- Adjustable hash size for MyISAM and Aria. This can greatly improve shutdown time (from hours to minutes) if you are using a lot of MyISAM/Aria tables with delayed keys — new in 10.0.13
- CHECKSUM TABLE is faster.
- We improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big result sets with all characters between 0x00-0x7f.
- Pool of Threads in MariaDB 5.1 and even better in MariaDB 5.5. This allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
- Lots of speed improvements when a client connects to MariaDB. Many of the improvements were done in MariaDB 10.1 and MariaDB 10.2.
- There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
- Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should give a speedup when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn't have to write the temporary rows to disk.
- The test suite has been extended and now runs much faster than before, even though it tests more things.
Extensions & New Features
MariaDB Server includes a number of new features and extensions. When the community submits patches or features that are found to be useful, safe, and stable, every effort is made to integrate them with and include in MariaDB. Below is a list of the most notable of these new features:
- Window functions — new in 10.2
- Number of supported decimals in DECIMAL has increased from
38— new in 10.2
- Recursive Common Table Expressions — new in 10.2
- New WITH statement.
WITHis a common table expression that allows you to refer to a subquery expression many times in a query — new in 10.2
- CHECK CONSTRAINT — new in 10.2
- DEFAULT expression, including
TEXT— new in 10.2
- Added catchall for list partitions — new in 10.2
- Oracle-style EXECUTE IMMEDIATE statement — new in 10.2
- Lots of new JSON functions — new in 10.2
- Microsecond Precision in Processlist
- Table Elimination
- Virtual Columns — new in 5.2
- Microseconds in MariaDB — new in 5.3
- Extended User Statistics — new in 5.2
- KILL all queries for a user — new in 5.3,
- KILL QUERY ID - terminates the query by query_id, leaving the connection intact — new in 10.0.5,
- Pluggable Authentication — new in 5.2
- Storage-engine-specific CREATE TABLE — new in 5.2
- Enhancements to INFORMATION SCHEMA.PLUGINS table — new in 5.2
- Group commit for the binary log. This makes replication notably faster! — new in 5.3
mysqlbinlog option to change the used database — new in 5.2
- Progress reporting for
LOAD DATA INFILE— new in 5.3
- Faster joins and subqueries — new in 5.3
- HandlerSocket and faster HANDLER calls — new in 5.3
- Dynamic Columns support — new in 5.3
- GIS Functionality — new in 5.3
- Multi-source replication — new in 10.0
- Global Transaction ID — new in 10.0
- SHOW EXPLAIN gives the EXPLAIN plan for a query running in another thread. — new in 10.0
- Roles — new in 10.0
- PCRE Regular Expressions (including
REGEXP_REPLACE()) — new in 10.0
- DELETE ... RETURNING — new in 10.0
- MariaDB supports more collations than MySQL.
For more information of the specific features introduced in each new version of MariaDB, see MariaDB Release.
Improved Quality Assurance
In the interest of providing a superior product, MariaDB Server utilizes its own testing suite for evaluating new features and patches. This suite includes many more tests and bugs found in these tests are fixed before the code ships. Furthermore, the tests are run against several configurations of MariaDB Server, to better test the feature in different environments, and are not run when the feature is not available in the given configuration.
Of bugs found otherwise, each release attempts to address and correct bugs and to avoid introducing new issues or breakages. Compiler warnings are also considered bad and efforts are made to eliminate as many compiler warnings as possible.
Truly Open Source
- All code in MariaDB is released under GPL, LGPL or BSD.
- MariaDB does not have closed source modules like the ones that can be found in MySQL Enterprise Edition. In fact, all the closed source features in MySQL 5.5 Enterprise Edition are found in the MariaDB open source version.
- MariaDB client libraries (for C, for Java (JDBC), for Windows (ODBC)) are released under LGPL to allow linking with closed source software. MySQL client libraries are released under GPL that does not allow linking with closed source software.
- MariaDB includes test cases for all fixed bugs. Oracle doesn't provide test cases for new bugs fixed in MySQL 5.5.
- All bugs and development plans are public.
- MariaDB is developed by the community in true open source spirit.