In MySQL, what are the practical differences between the InnoDB and MyISAM storage engines, and why is InnoDB the default?
answer
- the engine is chosen per table
- transactions and crash recovery
- row locks versus table locks
- a table marked as crashed
- ALTER TABLE ... ENGINE=InnoDB
basics
~20 sMySQL hands storage to an engine chosen per table. InnoDB, the default, gives transactions, row-level locking, foreign keys and automatic crash recovery; MyISAM has none of these, locks whole tables and can be left marked as crashed.
solid answer
~40 sMySQL splits the server (parser, optimizer, SQL layer) from a pluggable storage engine picked per table with `ENGINE=`; in 8.4 `default_storage_engine` is `InnoDB`. **InnoDB** is transactional: `COMMIT` and `ROLLBACK`, row-level locks, consistent nonlocking reads built from undo logs, foreign keys, and crash recovery from its redo log when the server restarts. It stores rows in a clustered index on the primary key and caches data and index pages in its buffer pool. **MyISAM** keeps each table as a `.MYD` data file and a `.MYI` index file, takes table-level locks so a writer blocks readers, has no transactions or foreign keys, and caches only index blocks in `key_buffer_size`. A crash mid-write can leave a MyISAM table marked as crashed until `REPAIR TABLE` fixes it. That is why InnoDB has been the default since MySQL 5.5.
code
sql · 9 linesSELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');
CHECK TABLE reports.daily_sales;
REPAIR TABLE reports.daily_sales;
ALTER TABLE reports.daily_sales ENGINE = InnoDB;go deeper
Recall that the engine is chosen per table, that InnoDB is the default, and the headline differences: transactions, row locks, foreign keys and crash recovery versus none of them.
Explain why table-level locking hurts mixed read and write load, why MyISAM can be marked as crashed while InnoDB recovers itself from its redo log, and how ALTER TABLE ... ENGINE=InnoDB converts a table.
Show you can run the migration: find stray MyISAM tables, repair before converting, add primary keys, move memory from the key cache to the buffer pool and audit code that assumed no rollback.
Frame the standing decision: enforce InnoDB-only schemas, keep NO_ENGINE_SUBSTITUTION on, and treat any remaining MyISAM table as a durability risk with an owner and a removal date.
## The pluggable storage engine layer MySQL is built in two layers. The **server layer** parses SQL, checks privileges, optimizes and executes the plan. Underneath it, a **storage engine** decides how rows are written to disk, cached, locked and recovered. The engine is chosen **per table**, not per server: `CREATE TABLE t (...) ENGINE = InnoDB` or `ENGINE = MyISAM`, and `SHOW ENGINES` lists what the server supports. When a statement names no engine, MySQL uses `default_storage_engine`, which is `InnoDB` in MySQL 8.4 and has been since 5.5, when it replaced MyISAM. Two details catch people: - If you name an engine the server does not have, MySQL can substitute the default engine and issue only a warning. The `NO_ENGINE_SUBSTITUTION` SQL mode, part of 8.4's default `sql_mode`, turns that into an error. - A server can mix engines freely, so one schema may hold InnoDB order tables beside a forgotten MyISAM reporting table. `information_schema.TABLES` has an `ENGINE` column that shows each table's engine. ## What InnoDB gives you **InnoDB** is the transactional engine, and nearly every feature people expect from a relational database lives in it: - **Transactions**: `START TRANSACTION`, `COMMIT`, `ROLLBACK` and savepoints, with ACID guarantees. - **Row-level locking**: concurrent writers block each other on the index records they lock, and InnoDB never escalates those locks to a table lock. - **MVCC**: plain `SELECT` statements read a consistent snapshot rebuilt from undo logs, so readers do not block writers. - **Crash recovery**: after an unexpected exit you simply restart; InnoDB replays its redo log and rolls back transactions that were not committed. - **Foreign keys** with referential actions. - A **clustered index**: rows live in the primary-key B-tree, and the **buffer pool** caches both data and index pages. ## What MyISAM lacks **MyISAM** is the older engine. Each table is two files, `.MYD` for rows and `.MYI` for indexes, and its design shows its age: - **Table-level locking.** An `UPDATE` locks the whole table, so one slow write stalls every reader of that table. - **No transactions.** `START TRANSACTION` is accepted, but `ROLLBACK` cannot undo MyISAM changes and only warns "Some non-transactional changed tables couldn't be rolled back". - **No foreign keys and no MVCC.** - **No crash safety.** A killed `mysqld`, a power loss or a hardware fault in the middle of a write can corrupt the table. Queries then fail with an error saying the table is marked as crashed and should be repaired (handler error 145), or with "Incorrect key file for table". - **Index-only caching.** `key_buffer_size` caches index blocks; row data relies on the operating system's file cache. | Feature | InnoDB | MyISAM | |---|---|---| | Transactions | Yes | No | | Locking granularity | Row | Table | | MVCC | Yes | No | | Foreign keys | Yes | No | | Crash recovery | Automatic, from the redo log | `CHECK TABLE` / `REPAIR TABLE` | | Clustered index | Yes | No | | Data cache | Buffer pool (data and indexes) | Index blocks only | | FULLTEXT indexes | Yes | Yes | One MyISAM advantage is often cited: an unfiltered `SELECT COUNT(*) FROM t` on a single table returns instantly because the row count is stored. InnoDB has to count rows visible to the current transaction by scanning an index. That rarely outweighs losing crash safety. ## A legacy reporting schema after a crash A typical scene: a reporting schema created years ago on MyISAM, a power cut, and the next morning several tables reported as marked as crashed. The recovery and the cure are separate steps: 1. Confirm the damage with `CHECK TABLE`, then run `REPAIR TABLE` (or `myisamchk` with the server stopped). Repair usually recovers the table, but rows from the interrupted write may be lost or need re-loading. 2. Find every remaining MyISAM table through `information_schema.TABLES`. 3. Convert each with `ALTER TABLE t ENGINE = InnoDB`, which rebuilds the table. Give each table a primary key first, because InnoDB clusters rows on it. 4. Shift memory from `key_buffer_size` towards InnoDB's buffer pool, because the converted tables are now cached there. 5. Review application code that assumed autocommit-per-statement behaviour: InnoDB now honours `ROLLBACK`, and long-open transactions start to cost something. After conversion the same power cut becomes routine: InnoDB replays its redo log at startup, rolls back unfinished transactions and the tables open consistent.
- Why can SELECT COUNT(*) FROM t with no WHERE clause be instant on MyISAM but slow on InnoDB?MyISAM stores the exact row count, so an unfiltered single-table `COUNT(*)` reads that number. InnoDB is multi-versioned: different transactions can see different row counts, so it counts rows visible to the current transaction by traversing the smallest secondary index, or the clustered index if there is none. `SHOW TABLE STATUS` gives an approximate count, or keep a counter table if an exact figure is needed often.
- What does the NO_ENGINE_SUBSTITUTION SQL mode protect you from?If `CREATE TABLE` or `ALTER TABLE` names an engine that is not compiled in or is disabled, MySQL without this mode creates the table with the default engine and issues only a warning, so a table you think is one engine is another. With `NO_ENGINE_SUBSTITUTION`, which MySQL 8.4 includes in its default `sql_mode`, the statement fails instead.
- Can a FULLTEXT search table be moved from MyISAM to InnoDB?Yes. InnoDB has supported `FULLTEXT` indexes since MySQL 5.6, so full-text search is no longer a reason to keep MyISAM. The query syntax with `MATCH ... AGAINST` stays the same, though relevance ranking and stopword handling differ between the two engines, so check result quality after converting.
- MyISAM is faster for reads, so it is the right default for reporting tables.
- The storage engine is set once per server and every table uses it.
- Wrapping MyISAM statements in START TRANSACTION makes them rollbackable.
- Full-text search only works on MyISAM, so those tables cannot move to InnoDB.
- A MyISAM table marked as crashed is lost and must come from a backup.
- InnoDB needs manual repair commands after a crash, just like MyISAM.