InnoDB vs MyISAM Storage Engines
Fast recall
Quick revision
Core answer
Tests understanding of MySQL internals, when to choose the right storage engine, and awareness of transactional guarantees critical for production Java applications.
Tests understanding of MySQL internals, when to choose the right storage engine, and awareness of transactional guarantees critical for production Java applications.
Tests understanding of MySQL internals, when to choose the right storage engine, and awareness of transactional guarantees critical for production Java applications.
Interview-ready guide
Interview answer
InnoDB and MyISAM are the two MySQL storage engines that come up most in interviews — InnoDB has been the default since MySQL 5.5 and the right choice for virtually every modern application.
InnoDB — the default for good reason
- InnoDB supports full ACID transactions, which means a multi-statement operation like debit account A and credit account B either both succeed or both roll back on crash — something MyISAM simply cannot do
- Row-level locking means concurrent writers block each other only when they touch the same row; MyISAM uses table-level locking, which means a single UPDATE blocks every other write on the table
- InnoDB enforces foreign key constraints at the database level, which means referential integrity is guaranteed even if the application has a bug; MyISAM ignores foreign key declarations entirely
MyISAM — where it still exists
- MyISAM was the historical default before MySQL 5.5 and is still present in legacy codebases; its main advantage was slightly faster reads on simple SELECT-heavy workloads with no concurrent writes
- Full-text search in older MySQL versions required MyISAM — but InnoDB has supported full-text indexes since MySQL 5.6, removing the last real reason to use MyISAM in new development
- If you encounter a MyISAM table in an existing system, the migration path to InnoDB is
ALTER TABLE t ENGINE=InnoDB— it rebuilds the table with full InnoDB capabilities
For any new table in a Spring Boot application: InnoDB. The transaction and row-locking guarantees are non-negotiable for any data that matters, and full-text search, spatial queries, and all other specialized capabilities are available in InnoDB today.
InnoDB vs MyISAM Storage Engines
Direct Answer
InnoDB is the default MySQL engine offering ACID transactions, foreign keys, row-level locking, and crash recovery. MyISAM is an older engine with faster read performance for non-transactional workloads but lacks transactions, foreign key support, and uses table-level locking. For most modern Java/Spring Boot applications, InnoDB is the correct choice.
Code Example
The storage engine is chosen per table. Watching a ROLLBACK succeed on InnoDB and silently no-op on MyISAM is the fastest way to understand why InnoDB is the default.
-- InnoDB: ACID, row-level locking, foreign keys, crash-safe
CREATE TABLE orders_innodb (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
-- MyISAM: non-transactional, table-level locking, no foreign keys
CREATE TABLE orders_myisam (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
/* foreign keys are silently ignored on MyISAM */
) ENGINE=MyISAM;
-- ---- Behavioral difference: transactions ----
START TRANSACTION;
INSERT INTO orders_innodb (user_id, total) VALUES (1, 50.00);
INSERT INTO orders_myisam (user_id, total) VALUES (1, 50.00);
ROLLBACK;
SELECT COUNT(*) FROM orders_innodb; -- 0 (rolled back — row gone)
SELECT COUNT(*) FROM orders_myisam; -- 1 (MyISAM ignored the transaction!)
-- ---- Behavioral difference: concurrency ----
-- Session 1:
UPDATE orders_innodb SET status = 'PAID' WHERE id = 1; -- row lock, blocks only row 1
UPDATE orders_myisam SET status = 'PAID' WHERE id = 1; -- TABLE lock, blocks ALL writers
-- ---- Migrating MyISAM → InnoDB ----
ALTER TABLE legacy_table ENGINE=InnoDB;
| Feature | InnoDB | MyISAM |
|---|---|---|
| Transactions | yes (ACID) | no |
| Locking | row-level | table-level |
| Foreign keys | enforced | ignored |
| Crash recovery | redo log + undo log | rebuild from .MYI index (slow, risky) |
| Full-text search | yes (MySQL 5.6+) | yes |
| Storage cost | slightly higher | slightly lower |
- InnoDB is the default since MySQL 5.5 and the only sensible choice for any application that mutates data. MyISAM survives for read-only archives and some niche
COUNT(*)workloads because it keeps the row count in the table header. ROLLBACKon a MyISAM table fails silently — no error, no rollback — a silent correctness bug waiting to happen.- Migrate legacy MyISAM tables incrementally with
ALTER TABLE … ENGINE=InnoDB. Expect a table copy; do it in a maintenance window or usept-online-schema-changefor zero-downtime.
Common Mistakes & Pitfalls
Tests understanding of MySQL internals, when to choose the right storage engine, and awareness of transactional guarantees critical for production Java applications.
Check your understanding
Answer these without looking back at the article.
- How does InnoDB implement MVCC under the hood?
- When would you consider using the MEMORY storage engine?
- How do you migrate an existing MyISAM table to InnoDB with zero downtime?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.