MariaDB is celebrating its 15th anniversary, marking a milestone for this widely used open source database. MariaDB was created in 2009 as a fork of MySQL and was developed by the original MySQL developers under the leadership of Michael “Monty” Widenius. The reason for this move was concern about the future of MySQL following its acquisition by Oracle.
Over the years, MariaDB has evolved into a robust and powerful database system known for its speed, innovation and transparency. It has become a favourite among developers and database administrators worldwide. While MySQL remains the second most popular database worldwide, MariaDB stands out with its advanced features and faster performance in many use cases.
To honour the development of MariaDB, we have compiled a list of the 15 most popular features and a comparison with MySQL to illustrate the differences.
What it is: Instantly add a new column to a table without rewriting the data.
MariaDB: Supported from version 10.3.
MySQL: Supported from version 8.0.13.
Main difference: MariaDB implemented this function earlier, and it is consistently more efficient with large tables.
________________________________________
2. Dynamic columns
What it is: Store semi-structured data in a structured table without changing the schema by using key-value pairs.
MariaDB: Fully supported from the start, with functions like COLUMN_CREATE and COLUMN_GET.
MySQL: Not natively supported. JSON functions are used as a workaround.
Main difference: MariaDB offers native support, which makes it easier to handle semi-structured data.
Example:
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
attributes BLOB
);
INSERT INTO products (name, attributes)
VALUES
('T-Shirt', COLUMN_CREATE('color', 'red', 'size', 'M', 'material', 'cotton'));
________________________________________
3. Invisible columns
What it is: Hidden columns that do not appear in SELECT * queries, but are accessible when explicitly called.
MariaDB: Supported from version 10.3.
MySQL: Not supported.
Main difference: MariaDB simplifies backward compatibility with this function.
Example:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
total_amount DECIMAL(10, 2),
internal_notes TEXT INVISIBLE
);
INSERT INTO orders (customer_name, total_amount, internal_notes)
VALUES
('Alice', 120.50, 'Priority customer'),
('Bob', 75.00, 'Delayed payment');
SELECT * FROM orders; --invisible column won´t be shown
________________________________________
4. Online schema changes
What it is: Changing table structures without locks or downtime.
MariaDB: Supported with ALGORITHM=INPLACE and LOCK=NONE.
MySQL: Supported from version 5.6, but with fewer options to avoid locks.
Main difference: MariaDB is more flexible and efficient for schema changes in large databases.
Example:
ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;
________________________________________
5. JSON functions
What it is: Native support for storing and querying JSON data.
MariaDB: Full support from version 10.2 with JSON functions and indices.
MySQL: Supported from version 5.7.
Main difference: MariaDB supplements JSON with Dynamic Columns for additional flexibility.
Example:
INSERT INTO data_store (data) VALUES ('{"key": "value"}');
SELECT JSON_EXTRACT(data, '$.key') FROM data_store;
________________________________________
6. Role-based access control (RBAC)
What it is: Grouping of privileges into roles to simplify rights management.
MariaDB: Supported from version 10.0.5.
MySQL: Only available in the Enterprise Edition from version 8.0.
Main difference: MariaDB contains RBAC in its open source version.
Example:
CREATE ROLE read_only;
GRANT SELECT ON database.* TO read_only;
GRANT read_only TO user1;
________________________________________
7. Partitioning improvements
What it is: Enhanced partitioning options for better query optimisation and management.
MariaDB: Supports subpartitioning and more partition types.
MySQL: Basic partitioning is supported, but subpartitioning is not available.
Main difference: MariaDB’s partitioning is more versatile, especially for large amounts of data.
________________________________________
8. Window functions
What it is: Performs aggregate calculations over rows without grouping.
MariaDB: Fully supported as of version 10.2.
MySQL: Supported from version 8.0.
Main difference: Both databases support window functions equally well.
Example:
SELECT id, SUM(amount) OVER (PARTITION BY category) AS category_total FROM sales;
________________________________________
9. Temporary tablespaces
What it is: Store temporary table data in custom tablespaces for better control.
MariaDB: Supported from version 10.4.
MySQL: Not supported.
Main difference: MariaDB offers more flexibility in managing temporary data storage.
________________________________________
10. Data encryption on multiple levels
What it is: Encrypts data at table, column or tablespace level.
MariaDB: Supported from version 10.1.
MySQL: Only available in the Enterprise Edition.
Main difference: MariaDB offers comprehensive encryption in its open source version.
Example:
CREATE TABLE confidential_data
(id INT PRIMARY KEY,
secret VARCHAR(255))
ENCRYPTED=YES;
________________________________________
11. Thread pooling
What it is: More efficient management of threads in high concurrency environments.
MariaDB: Fully supported with advanced settings.
MySQL: Only available in the Enterprise Edition.
Main difference: MariaDB offers free thread pooling, which improves scalability for large applications.
________________________________________
12. ColumnStore for analyses
What it is: A storage engine optimised for analytical queries with column-based storage.
MariaDB: Supported from version 10.5.
MySQL: Not available.
Main difference: MariaDB enables hybrid transactional and analytical processing (HTAP).
________________________________________
13. Aria storage engine
What it is: A crash-safe storage engine optimised for temporary tables and complex queries.
MariaDB: Fully supported. Often used internally for metadata and queries.
MySQL: Not available.
Main difference: MariaDB’s Aria engine provides higher security for temporary and intermediate operations.
________________________________________
14. Audit plugin
What it is: Logs database activity for compliance and security audits.
MariaDB: Fully supported in the community edition.
MySQL: Only available in the Enterprise Edition.
Main difference: MariaDB provides this function free of charge.
INSTALL SONAME 'server_audit';
________________________________________
15. Faster query execution
MariaDB: Due to optimised query execution paths, MariaDB is often more powerful than MySQL.
MySQL: Has been significantly improved in version 8.0, but is often slower than MariaDB for read-intensive workloads.
Main difference: The speed of MariaDB is still a major advantage for high-performance applications.
________________________________________
Conclusion
MariaDB’s 15-year history is characterised by innovation, speed and a commitment to open source principles. Its features such as Dynamic Columns, Instant ADD COLUMN and Thread Pooling make it a robust and flexible database solution.
Although MySQL is still very popular, MariaDB is gaining traction with developers and organisations, proving to be a faster and more adaptable alternative. Here’s to the next 15 years of MariaDB as an excellent database!
If you’re considering switching to MariaDB or need expert guidance to optimise your database infrastructure, Baremon is here to help. With our years of experience in database consulting, including installation, migration, performance tuning, and security, we can ensure your database runs seamlessly.
Contact us today to learn how we can support your database transformation and help you leverage MariaDB’s full potential.
