MySQL interview question · Most asked
Why is InnoDB the default storage engine, and how does it differ from MyISAM?
Summary
InnoDB is transactional (ACID), uses row-level locking and MVCC, supports foreign keys, and recovers automatically after a crash. MyISAM has none of these: it locks whole tables on writes and can be corrupted by a crash.
Longer Answer
InnoDB writes changes to a redo log before data pages (write-ahead logging), so committed transactions survive crashes. Readers do not block writers thanks to MVCC, and writers lock individual rows (plus gaps under REPEATABLE READ). Data is stored in a clustered index on the primary key.
MyISAM stores data and indexes separately, has table-level locks (a long write blocks all reads), no transactions and no foreign keys. Its historical advantages (fast COUNT(*), full-text search) no longer matter: InnoDB has had full-text indexes since 5.6. Use InnoDB for everything except special cases (MEMORY for temporary lookup data, ARCHIVE).
Key Points
- InnoDB: ACID, row locks, MVCC, FKs, crash recovery.
- MyISAM: table locks, no transactions.
- InnoDB clusters data by primary key.
More most-asked MySQL questions
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- How does a composite index work, and what is the leftmost-prefix rule?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How do you read EXPLAIN output in MySQL?
- What causes deadlocks in MySQL, and how do you handle them?
- Why might MySQL not use an index you created?
- How does MySQL replication work? Explain the binary log, asynchronous and semi-synchronous replication, and GTIDs.