57
What is the difference between the InnoDB and MyISAM storage engines?
Level: Advanced — A must-know comparison; InnoDB is the modern default for very good reasons.
MySQL supports pluggable storage engines — the underlying software that handles how data is stored and retrieved. The two most famous are InnoDB (the default since MySQL 5.5) and MyISAM (the older, legacy engine).
- InnoDB: Transaction-safe, ACID-compliant, supports foreign keys and row-level locking. Best for high-concurrency, write-heavy applications.
- MyISAM: Faster for simple, read-heavy workloads, but has no transactions, no foreign keys, and only table-level locking.
Bottom line: Use InnoDB for almost everything today — it's the default and is safer, more reliable, and better for concurrency. MyISAM is mostly legacy or used for specific read-only/analytical cases.
Side-by-Side Comparison
| Feature |
InnoDB |
MyISAM |
| Transactions (ACID) |
Yes |
No |
| Foreign keys |
Yes |
No |
| Locking level |
Row-level |
Table-level |
| Crash recovery |
Strong (redo logs) |
Weak (can corrupt) |
| Concurrency |
High |
Low (table locks) |
| Full-text search |
Yes (5.6+) |
Yes |
| Best for |
Write-heavy, OLTP, concurrency |
Read-heavy, simple apps |
Why Locking Matters
InnoDB — Row-Level Locking
- Only the affected rows are locked
- Multiple users can write concurrently
- Great for busy transactional systems
MyISAM — Table-Level Locking
- The entire table locks on a write
- Writers block all other access
- Bottleneck under heavy concurrency
Quick Example
-- Specify the engine when creating a table
CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2)
) ENGINE = InnoDB;
-- Legacy MyISAM table
CREATE TABLE logs (
id INT PRIMARY KEY,
msg TEXT
) ENGINE = MyISAM;
-- Check the engine of existing tables
SHOW TABLE STATUS WHERE Name = 'orders';
-- Convert an existing table to InnoDB
ALTER TABLE logs ENGINE = InnoDB;
Interviewer tip: The one-liner they want — "InnoDB supports ACID transactions, foreign keys, and row-level locking (ideal for concurrency and write-heavy apps), while MyISAM lacks transactions and foreign keys and uses table-level locking. InnoDB is the modern default."