MyISAM: The Legacy Engine

MyISAM was MySQL’s default storage engine until MySQL 5.5 (2010). Many old tutorials, plugins and hosting setups still mention it. It is a legacy engine: still present in MySQL 8 and 9 (the system tables no longer use it), but not recommended for any new table.

What MyISAM lacks

1
2
3
4
5
6
7
8
9
10

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20)) ENGINE = InnoDB;

CREATE TABLE orders_myisam (
    id INT PRIMARY KEY,
    customer_id INT,
    total INT,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE = MyISAM;

SHOW CREATE TABLE orders_myisam\G

1
2
3
4
5
6
7
8
9
10

*************************** 1. row ***************************
       Table: orders_myisam
Create Table: CREATE TABLE `orders_myisam` (
  `id` int NOT NULL,
  `customer_id` int DEFAULT NULL,
  `total` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

The FOREIGN KEY clause was silently ignored: it does not appear in the table definition. MyISAM accepts the syntax and does nothing. That is a trap for people who think their data is protected.

1
2

INSERT INTO orders_myisam VALUES (1, 999, 50);
SELECT * FROM orders_myisam;

1
2
3
4
5
6

+----+-------------+-------+
| id | customer_id | total |
+----+-------------+-------+
|  1 |         999 |    50 |
+----+-------------+-------+
1 row in set

The order for the non-existent customer 999 was accepted. With InnoDB, it would be refused.

No transactions

1
2
3
4

START TRANSACTION;
INSERT INTO orders_myisam VALUES (2, 1, 70);
ROLLBACK;
SELECT COUNT(*) AS orders_after_rollback FROM orders_myisam;

1
2
3
4
5
6

+-----------------------+
| orders_after_rollback |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set

Two rows: the ROLLBACK did nothing. The row inserted in the “transaction” stayed, because MyISAM has no transactions. Each statement is applied at once and cannot be undone. With a failure halfway through a multi-statement job, you are left with half the changes.

The full comparison

Table-level locking

A write to a MyISAM table takes a lock on the whole table. Other queries on that table, reads included, wait. On a busy site, one slow UPDATE or ALTER blocks everything. InnoDB locks only the rows involved.

Crash recovery

After a power loss, a MyISAM table may be corrupted and flagged as “crashed”. You then run REPAIR TABLE, which may lose rows. InnoDB replays its redo log and returns to a consistent state automatically.

Is there a reason to use MyISAM today?

Almost none:

• “It’s faster for reads.” This was true on very old hardware and workloads. InnoDB’s buffer pool, clustered index and MVCC make it as fast or faster for almost all real workloads, and much faster under concurrent writes.
• “COUNT(*) is instant.” Keep a counter, or use a summary table (Summary Tables).
• “Full-text search.” InnoDB has it.
• “Smaller files.” Not worth the loss of safety.

Even the MySQL manual calls InnoDB the preferred engine. The mysql system schema moved to InnoDB in MySQL 8.0, which also removed most reasons for MyISAM to exist.

Where you still meet it

• Old dumps and legacy applications with ENGINE=MyISAM in their CREATE TABLEs.
• Very old WordPress installations and plugins (current WordPress uses InnoDB).
• Hosting accounts with outdated defaults.
• Merge tables (MRG_MYISAM): a legacy way of presenting several MyISAM tables as one.

What to do

1. Find MyISAM tables (Check all tables).
2. Convert them to InnoDB (Converting to InnoDB).
3. Fix the source: remove ENGINE=MyISAM from your scripts and from the dumps you restore from.
4. Check default_storage_engine on every server.