MySQL is probably the most famous Relational Database Management System (RDBMS). Developed as a free and open source software, it was originally backed by the MYSQL AB company, but is now owned by Oracle. In MySQL the “storage engine” used for a table determines how data is handled. There are several storage engines available, but the most used are InnoDB and MyISAM. In this article we see what are their distinctive features and the main differences between them.
In this tutorial you will learn:
- What is a storage engine
- How to check what storage engines are available
- The main differences between MyISAM and InnoDB
- How to check what engine is used by a table
- How to set and change the storage engine used by a table
Software requirements and conventions used
| Category | Requirements, Conventions or Software Version Used |
|---|---|
| System | Distribution-independent |
| Software | No specific software needed |
| Other | None |
| Conventions | # – requires given linux-commands to be executed with root privileges either directly as a root user or by use of sudo command$ – requires given linux-commands to be executed as a regular non-privileged user |
What is a storage engine?
Before we discuss the features and differences between the two main MySQL storage engines, we should define what a storage engine is. Storage engines, also known as “table handlers”, are basically the database parts which interpret and manages operations related to SQL queries for database tables. In recent versions of MySQL, storage engines can be organized and managed using a “pluggable” architecture. A variety of storage engines exists, but the two more frequently used are InnoDB and MyISAM.
Checking the available storage engines
To get a list of the available storage engines in the database we are using, all we have to do is issue a simple SQL query, therefore the first thing we need to do is to open a MySQL interactive prompt and log in using a database user and its password:
$ mysql -u <username> -p<password>
If the login is successful, the prompt will change to
mysql>. Here we can run our SQL query to visualize the available storage engines:
mysql> SHOW ENGINES;
Once the query is executed, we should obtain a result similar to the following:
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
In the table above, generated as the result of the query, we can easily see what storage engines are supported, by taking a look at the value in the Support column in each row. The “YES” value means the storage engine is available, “NO” otherwise. The “DEFAULT” value in the same column, instead, indicates that the corresponding engine, in this case InnoDB, is the default one used by the server.
The values existing in the “Transactions” and “Savepoints” columns, indicates whether a storage engine supports, respectively, transactions and rollbacks or not. As we can see by taking a look at the table, only the InnoDB engine does.
Information about storage engines exists in the “ENGINES” table of the “INFORMATION_SCHEMA” database, therefore we can also issue standard “SELECT” queries to obtain the data we need:
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
We would obtain the same result we saw above.
InnoDB vs MyISAM
Let’s see what are the main features and differences between the two most used storage engines: InnoDB and MyISAM.
InnoDB
As we already said, InnoDB is the default storage engine since MySQL 5.5. Some of the main features of this storage engine are the following:
- Support for transactions with commit and rollback
- Row-level locking
- Foreign key support, with cascading update and delete
Transactions with rollbacks and commits
The support for transactions provides a safe way to execute multiple queries keeping data consistent. When multiple operations that modify data are executed and we want to make sure they are effective only if all of them succeed and no errors occur, we want to use transactions. The typical way of proceeding is to start a transaction and performing the queries: if some error arises, a rollback is performed, otherwise the changes are commited.
Row-level locks
When using InnoDB data locking happens at row level, so the amount of data that is locked during a transaction is limited. There are two types of locks with InnoDB:
- Shared lock
- Exclusive lock
A shared lock allows the transaction who owns it to read the row, while an exclusive lock allows the transaction to perform operations which modify the row, so to update or delete data.
When a transaction gets a shared lock on a row, and another transaction requires the same lock type, it is granted immediately; if the second transaction, however, requests an exclusive lock on the same row it will have to wait.
If the first transaction holds an exclusive lock on the row, instead, the second one will have to wait for said lock to be released to get either a shared or exclusive lock.
Foreign keys support
Foreign keys are a very important feature, since they can be used to enforce data integrity based on the logic relation between tables. Imagine we have three tables in our database (suppose it is called “testdb”): a user table containing existing users, a job table where all the available jobs are registered, and a user_job table used to represent the many to many relationship which exist between users and jobs (a user can have multiple jobs, and multiple jobs can be associated with the same user).
The user_job table is what is called a join or association table, since its sole purpose is to represent the users-jobs associations. The table has two columns, one called user_id and the other job id. Two foreign key constraint would exist in the table, to enforce the following rules: a value in the user_id column can only reference a value in the id column of the user table, and a value in the job_id column must reference an existing one in the id column of the job table.
This would enforce integrity, since only ids of existing users and jobs would be allowed to exist in the association table. Deleting a user or a job involved in one or more associations in the user_job table, would also not be allowed, unless a CASCADE DELETE rule is set for the corresponding foreign key. In that case, when a user or a job would be deleted, the relationships they are involved in would also be removed.
MyISAM
MyISAM used to be the default MySQL storage engine, but has been replaced by InnoDB. When this engine is used, data locks happen at table level, therefore more data is locked when an operation is performed. Unlike InnoDB, MyISAM doesn’t support transactions rollback and commits, so, rollbacks have to be performed manually. Another big difference between MyISAM and InnoDB is that the former doesn’t support foreign keys. MyISAM is simpler, and could have an advantage (debatable) on read-intensive operations on limited sets of data. When MyISAM is used on a table, a flag is set, which indicates if that table needs repairing, after, for example an abrupt shutdown. Table repairing could later be performed using the appropriate tools.
Checking what storage engine is used by a specific table
How to know what storage engine is used for a specific table? All we have to do is to issue a simple query. For example, to know what storage engine is used for the user table we mentioned in the previous example, we would run:
mysql> SHOW TABLE STATUS WHERE name = 'user' \G;
Notice that in the query above we used \G, in order to make the query result be displayed vertically, to optimize space. Once the query is executed we obtain the following result:
*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-12-27 09:38:16
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
In this case, by looking at the value stored in the “Engine” column, we can clearly see that the “InnoDB” engine is used for the table. An alternative way to obtain the same information is to query the INFORMATION_SCHEMA.TABLES table directly:
mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'testdb';
The query above would return only the engine used by the table:
+--------+ | ENGINE | +--------+ | InnoDB | +--------+
If we slightly change the query we can obtain a list of all the table names in the database and the engine used by them:
mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb';
Setting and changing the storage engine used by a table
If we want to set a specific storage engine for a table, we can specify it at creation time. For example, suppose we are creating the job table and for some reason we want to use the MyISAM storage engine for it. We would issue the following SQL query:
mysql> CREATE TABLE testdb.job ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL ) ENGINE = MyISAM;
If, instead, we want to change the storage engine in use for an already existing table, we simply need to use an ALTER SQL statement. Suppose we want to change the storage engine used for the “job” table we created in the previous example to InnoDB; we would run:
mysql> ALTER TABLE testdb.job ENGINE = InnoDB;
Conclusions
In this tutorial we learned what is a database storage engine, and we saw the main features of the two most used MySQL engines: InnoDB and MyISAM. We saw how to check what engines are available, what engine is used for a table and how to set and modify a table engine using SQL queries.
