Storage Engines

A storage engine is the code underneath a MySQL table that actually reads and writes data to disk — and unusually, MySQL lets you pick a different one per table.

InnoDB: the default, and almost always the right choice

InnoDB has been MySQL's default engine since version 5.5, and supports transactions, foreign keys, and row-level locking (so two connections can write to different rows of the same table at once without blocking each other):

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  balance DECIMAL(10,2)
) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

Because InnoDB is already the default, that ENGINE=InnoDB clause is usually unnecessary — but writing it explicitly makes the choice obvious to whoever reads the schema later.

MyISAM: the older engine

MyISAM predates InnoDB as MySQL's original default. It locks an entire table for writes (rather than just the affected rows), and supports neither transactions nor foreign keys — but it's still occasionally used for read-heavy, rarely-written tables:

CREATE TABLE audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message TEXT
) ENGINE=MyISAM;

SHOW TABLE STATUS WHERE Name IN ('accounts', 'audit_log');

+------------+--------+
| Name       | Engine |
+------------+--------+
| accounts   | InnoDB |
| audit_log  | MyISAM |
+------------+--------+
2 rows in set (0.01 sec)

SHOW TABLE STATUS is how you check which engine an existing table actually uses — useful when you've inherited a database and don't yet know its history.