-->

Last updated:

There are quite a few storage engines available for MariaDB. As at June 2021 the default storage engine is InnoDB. However, cPanel servers still use MyISAM by default. This article explains the main advantages of InnoDB, and why you may want to convert MyISAM tables to InnoDB.

The main advantages of InnoDB are that it supports row-level locking, foreign key constraints and transactions. I explain what that means in a second, but the long and short of it is that InnoDB is more advanced than MyISAM. That also makes InnoDB a little more complex, in particular when it comes to releasing space. For smallish websites that don’t get a huge amount of traffic MyISAM may be the right choice, but using InnoDB can make all the difference for large, high performance websites.

Row-level locking

Databases use “locking” for data integrity. On a busy website lots of SQL queries are executed, and you want to be sure they play nicely. For instance, if two processes try to update a value in a database then one of the processes has to wait until the other has finished. Similarly, if a process is busy updating a value then any process that wants to read the value has to wait until the update query has finished. In effect, database servers put in place a “lock” while an action is taking place.

MyISAM only supports table-level locking. This means that an entire table is locked while any data in the table is altered. InnoDB, on the other hand, supports row-level locking. That can be huge advantage, as it means that queries that don’t affect a locked row in a table can run at the same time.

Foreign key constraints

Another major advantage of InnoDB is that it supports foreign key constraints. Unless you use a database as a glorified spreadsheet your database probably has more than one table, and some of the fields in the tables are linked. For instance, you might have a products table that is linked to a table for suppliers. A constraint makes sure that the relationship between the tables can’t be broken.

This concept is a little abstract but very important. The below example shows what can happen if you don’t use constraints. I’m creating a database with two MyISAM tables; one for suppliers and one for products. I then populate the tables with some data.

CREATE DATABASE testdb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE testdb;

CREATE TABLE suppliers (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=MyISAM;

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  supplier INT NOT NULL,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=MyISAM;

INSERT INTO suppliers (name) VALUES 
('Wonka Industries'),
('Acme Corp.');

INSERT INTO products (supplier, name) VALUES 
(1, 'Wonkies'),
(2, 'Honkies'),
(1, 'Conkies');

So far, so good. We got two tables that are linked. If you want to list all products supplied by Wonka Industries then you can use a join:

SELECT p.name AS Product, s.name AS Supplier
FROM products p
INNER JOIN suppliers s
ON p.supplier = s.id
WHERE s.id = 1;
+---------+------------------+
| Product | Supplier         |
+---------+------------------+
| Wonkies | Wonka Industries |
| Conkies | Wonka Industries |
+---------+------------------+

However, the relationship between the id field in the suppliers table and the supplier field in the products table isn’t constrained. That means that you can simply delete Wonka Industries from the suppliers table. MariaDB won’t complain, even though the query leaves you with products that have no supplier:

DELETE FROM suppliers WHERE id = 1;
Query OK, 1 row affected (0.001 sec)

SELECT p.name AS Product, s.name AS Supplier
FROM products p
INNER JOIN suppliers s
ON p.supplier = s.id
WHERE s.id = 1;
Empty set (0.001 sec)

Adding a constraint

This is not a very good database design. Rather than just deleting a supplier you really want the database to check if the supplier is linked in the products table. You can’t do that with MyISAM but you can with InnoDB. Below is the same database, but this time using InnoDB as the storage engine. I have also defined the supplier field in the products database as a foreign key:

CREATE TABLE suppliers (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  supplier INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(supplier) REFERENCES suppliers(id)
) ENGINE=InnoDB;

If you populate the tables again and then try to delete Wonka Industries you get an error:

DELETE FROM suppliers WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplier`) REFERENCES `suppliers` (`id`))

As you can see, MariaDB complains that “a foreign key constraint fails”. In other words, before it deleted the supplier it checked if the supplier is referenced in the products table.

Transactions

InnoDB also implements transactions. This concept is again easiest to explain using an example. Below, I am inserting malformed data into an empty InnoDB suppliers table. Can you see why the INSERT query failed and why no records were inserted?

MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> INSERT INTO suppliers (name) VALUES
    -> ('Wonka Industries'),
    -> ('Acme Corp.');
Query OK, 2 rows affected (0.000 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ('Evil Biz'),
    -> ('Boogle Inc.');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Evil Biz'),
('Boogle Inc.')' at line 1

MariaDB [test]> ROLLBACK;

MariaDB [test]> SELECT COUNT(id) FROM suppliers;
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+

The issue with the INSERT query was that the “Acme Corp” line ended with a semi-colon rather than a comma. This is the query I tried to run:

INSERT INTO suppliers (name) VALUES 
('Wonka Industries'),
('Acme Corp.');
('Evil Biz'),
('Boogle Inc.');

So, MariaDB inserted the first two rows and then committed the query (as it saw a semi-colon). Next, it received the command ('Evil Biz'), ('Boogle Inc');. Obviously, that is not valid SQL and MariaDB therefore complained about a syntax error.

In some databases, such as PostgreSQL, the entire command fails if the SQL doesn’t make sense. MySQL and MariaDB, however, still insert the first two rows. You can avoid this odd behaviour using transactions, and that is exactly what I did by starting with the BEGIN statement. As the name suggests, BEGIN starts a new transaction (you can also use START TRANSACTION). Until you enter the COMMIT statement nothing is saved. In this case I ran into an error and I therefore used the ROLLBACK command to cancel the entire transaction.

You can run the same commands on a MyISAM table but BEGIN and ROLLBACK don’t do anything. After you enter the ROLLBACK command you will find that the first two rows have been inserted, even though you asked MariaDB to cancel the transaction.

MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> INSERT INTO suppliers (name) VALUES
    -> ('Wonka Industries'),
    -> ('Acme Corp.');
Query OK, 2 rows affected (0.000 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> ('Evil Biz'),
    -> ('Boogle Inc.');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Evil Biz'),
('Boogle Inc.')' at line 1

MariaDB [test]> ROLLBACK;
Query OK, 0 rows affected (0.011 sec)

MariaDB [test]> SELECT COUNT(id) FROM suppliers;
+-----------+
| COUNT(id) |
+-----------+
|         2 |
+-----------+

Is InnoDB always better than MyISAM?

Different storage engines serve different use-cases. The reason why cPanel servers default to MyISAM is that the storage engine is lightweight and less complex than InnoDB. MyISAM tables are easier to manage; you don’t have to worry about database tables growing ever larger and you can use the CHECK TABLE and REPAIR TABLE commands to deal with crashed tables. Data corruption is fairly rare with InnoDB but when it happens you are in for a treat.

In general, use InnoDB if performance and data integrity are your main priorities. InnoDB’s support for row-level locking is great for busy websites, and foreign key constraints and transactions help prevent data in your database gets mangled. Of course, the latter does imply your code takes advantage of these features. If you run a fairly simple website or a content management system that is happy with MyISAM then the benefits are likely to be negligible.