For any new table in MySQL 8, use InnoDB. It has been the default storage engine since MySQL 5.5, MySQL's own system tables use it, and it is the only common engine with transactions, foreign keys, row-level locking and automatic crash recovery. MyISAM still appears in old tutorials, legacy PHP applications and some college lab manuals, so it is worth knowing what it does differently, and the experiments below show those differences on a real MySQL 8.0 server rather than in theory.
What a storage engine is
MySQL separates the SQL layer (parsing, optimising) from the storage layer that actually writes rows to disk. Each table chooses an engine, and the engine decides how data is stored, locked and recovered. You can list what your server supports:
SELECT ENGINE, SUPPORT, TRANSACTIONS
FROM information_schema.ENGINES
WHERE ENGINE IN ('InnoDB','MyISAM','MEMORY','CSV','ARCHIVE')
ORDER BY ENGINE;
| ENGINE | SUPPORT | TRANSACTIONS |
|---|---|---|
| ARCHIVE | YES | NO |
| CSV | YES | NO |
| InnoDB | DEFAULT | YES |
| MEMORY | YES | NO |
| MyISAM | YES | NO |
Side-by-side comparison
| Feature | InnoDB | MyISAM |
|---|---|---|
| Transactions (COMMIT / ROLLBACK) | Yes | No |
| Foreign keys | Enforced | Parsed but ignored |
| Locking | Row-level, so many users can write at once | Whole table on every write |
| Crash recovery | Automatic, using the redo log | Tables can be marked crashed; needs REPAIR TABLE |
| Primary key storage | Clustered: rows stored in primary-key order | Heap file plus separate indexes |
SELECT COUNT(*) with no WHERE | Counts rows (can be slow on huge tables) | Instant, row count is stored |
| Full-text search | Yes (since MySQL 5.6) | Yes |
| Typical use today | Everything | Legacy tables, some read-only archives |
Experiment 1: ROLLBACK on both engines
CREATE TABLE acc_innodb (id INT PRIMARY KEY, balance INT) ENGINE=InnoDB;
CREATE TABLE acc_myisam (id INT PRIMARY KEY, balance INT) ENGINE=MyISAM;
INSERT INTO acc_innodb VALUES (1,1000);
INSERT INTO acc_myisam VALUES (1,1000);
START TRANSACTION;
UPDATE acc_innodb SET balance = 0 WHERE id = 1;
UPDATE acc_myisam SET balance = 0 WHERE id = 1;
ROLLBACK;
SHOW WARNINGS;
| Level | Code | Message |
|---|---|---|
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
SELECT 'InnoDB' AS engine, balance FROM acc_innodb
UNION ALL
SELECT 'MyISAM', balance FROM acc_myisam;
| engine | balance |
|---|---|
| InnoDB | 1000 |
| MyISAM | 0 |
The InnoDB change was undone; the MyISAM change was written immediately and stays. In a payment or inventory system that difference is the whole story. See COMMIT and ROLLBACK in MySQL for transactions in detail.
Experiment 2: foreign keys
CREATE TABLE dept_m (dept_id INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE emp_m (emp_id INT PRIMARY KEY, dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept_m (dept_id)) ENGINE=MyISAM;
INSERT INTO emp_m VALUES (1, 99); -- department 99 does not exist
SELECT * FROM emp_m;
| emp_id | dept_id |
|---|---|
| 1 | 99 |
MyISAM accepted an employee in a department that does not exist, without any error. The same tables on InnoDB:
CREATE TABLE dept_i (dept_id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE emp_i (emp_id INT PRIMARY KEY, dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept_i (dept_id)) ENGINE=InnoDB;
INSERT INTO emp_i VALUES (1, 99);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`shop`.`emp_i`, CONSTRAINT `emp_i_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept_i` (`dept_id`))
InnoDB protects referential integrity; with MyISAM your application has to do it. Keys are explained in primary key vs foreign key.
Locking: why MyISAM struggles with many users
When one session updates a MyISAM table, the whole table is locked for writing, and other sessions wanting to write wait in line. On a busy web app (say, a ticket booking page during a sale) that queue becomes the bottleneck. InnoDB locks only the rows being changed and uses multi-version concurrency control, so readers do not block writers and two users updating different rows proceed in parallel.
Checking and converting engines
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;
ALTER TABLE acc_myisam ENGINE = InnoDB; -- converts the table in place
SELECT @@default_storage_engine; -- InnoDB
Converting rebuilds the table, so on a large table run it in a maintenance window and take a backup first. After converting, existing data that breaks a foreign key will stop you adding that constraint, so clean orphans first (an anti-join with LEFT JOIN ... IS NULL finds them; see MySQL joins). If you omit ENGINE= in CREATE TABLE, you get the default, InnoDB.
Is there still any reason to choose MyISAM?
- A very old application that depends on MyISAM-only behaviour, until it can be migrated.
- Read-only lookup data where an instant
COUNT(*)matters and crash safety does not. Even here, InnoDB is usually fast enough, and a summary table can store counts.
The other engines have narrow uses: MEMORY for small throwaway lookup tables (data is lost on restart), ARCHIVE for compressed insert-only logs, CSV for exchanging files. How InnoDB keeps hot data in RAM is covered in MySQL caching and the buffer pool, and how engines fit into the server's design in MySQL architecture explained.