InnoDB vs MyISAM in MySQL: differences and choice

What really separates InnoDB from MyISAM

On a MySQL or MariaDB server, every table is stored and processed through a storage engine — a module that defines the on-disk file format, transaction support, and locking behavior. Since MySQL 5.5, InnoDB has been the default engine, but older projects and some schemas still carry MyISAM tables. The gap between InnoDB and MyISAM is not cosmetic: the engine choice decides whether a table survives an abrupt server restart without data loss or corruption.

Transactions and row-level locking

InnoDB writes changes to a transaction log and can roll back an unfinished operation. This matters for an online store: if a payment fails halfway through writing an order, InnoDB rolls back every related insert instead of leaving the order half-written. MyISAM knows no transactions at all — any operation is committed to disk immediately.

The second difference is lock granularity. MyISAM locks the entire table during a write, so parallel SELECT statements wait in a queue. InnoDB locks only the rows a query actually changes, and parallel readers and writers barely interfere thanks to MVCC.

What happens when the server crashes

After a power loss or a kill -9 of the mysqld process, InnoDB tables recover automatically on the next start: the server reads the redo log and replays uncommitted transactions. The administrator usually does not need to do anything by hand.

MyISAM behaves very differently: a table file can end up corrupted, and the server refuses to work with it until you run REPAIR TABLE or myisamchk. On large tables recovery takes hours, and in the worst case some rows are lost permanently. For backups that protect against this scenario, see the article on mysqldump backup and restore.

When MyISAM still makes sense

Despite its age, the engine keeps a few narrow use cases:

• Read-only tables — log archives and reference lists that never change after loading.
• Full-text search on old MySQL versions without built-in FULLTEXT support in InnoDB.
• One-off bulk imports where insert speed matters more and integrity is checked separately.
• Legacy CMS code that is hard-wired to MyISAM behavior and is not planned for a rewrite.

How to check which engine a table uses

The fastest way is SHOW TABLE STATUS or a query against information_schema:

SHOW TABLE STATUS FROM shop LIKE 'orders'\G

SELECT table_name, engine FROM information_schema.tables WHERE table_schema='shop';

If the Engine column shows MyISAM for a table that needs transactions, that is a reason to migrate. While you are at it, check the index types too — the article on MySQL index optimization explains how indexes behave differently on InnoDB and MyISAM.

How to convert a table from MyISAM to InnoDB

Take a backup before converting, then switch the engine with a single command:

ALTER TABLE shop.orders ENGINE=InnoDB;

On tables of several gigabytes the operation locks writes while it rebuilds the table and can take tens of minutes. For a production system with no downtime, use pt-online-schema-change from Percona Toolkit — it copies data in the background and swaps the table atomically. After converting, check the InnoDB buffer pool settings in the config — a separate article covers my.cnf tuning.

Bottom line: what to choose for your tables

For the vast majority of new projects the right choice is InnoDB, and MySQL 8.0 uses it by default with no extra settings needed. Before leaving MyISAM in an old schema, run through this checklist:

• Does the table need transactions or foreign keys — then only InnoDB will do.
• Does it need to survive an abrupt server restart without manual recovery.
• Is there concurrent writing from several connections at once.
• Is the table truly static and changed no more than once a day.
• Has the backup been verified before any engine migration.