InnoDB vs. MyISAM: Differences That Still Matter

1. Why the comparison still matters today

Since MySQL 5.5, InnoDB has been the default storage engine, and in new projects MyISAM is practically never used anymore. Still, you regularly encounter MyISAM tables in grown systems: old Magento 1 installations, migrated WordPress databases, home-built reporting tools from the 2000s, or simply tables that were created years ago with CREATE TABLE ... ENGINE=MyISAM and never migrated. The comparison of InnoDB vs. MyISAM is therefore not a historical topic but a practical necessity for anyone working with legacy databases.

The differences between the two engines are not cosmetic, they concern fundamental properties: whether a database supports transactions, how granular locks are set, and whether a system crash leads to data loss. Anyone who overlooks a MyISAM table in production can be badly surprised by these properties, for example when a single slow write blocks the entire table for every other request. The following sections work through the differences between InnoDB and MyISAM systematically.

2. Transactions and ACID: the fundamental difference

InnoDB is a fully transactional storage engine and satisfies the ACID properties: atomicity, consistency, isolation, durability. Several related changes can be bundled into one transaction and either committed entirely or rolled back entirely. MyISAM does not know this concept at all. A ROLLBACK on a MyISAM table has no effect, because MyISAM keeps no undo information. Every single SQL statement is applied immediately and permanently, regardless of whether a following statement fails.

For applications that execute several related write operations, for example creating an order and reducing stock at the same time, this is a substantial risk. If the second operation fails on MyISAM, the first one remains in place, and the database ends up in an inconsistent state that must be corrected manually. With InnoDB, the transaction prevents exactly this scenario, because COMMIT confirms both changes together, or ROLLBACK discards both together.

-- InnoDB: transaction guarantees atomicity across multiple statements
START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 4711;
INSERT INTO orders (product_id, customer_id, status) VALUES (4711, 88, 'new');
COMMIT;
-- If either statement fails, ROLLBACK undoes both changes cleanly

-- MyISAM: no transaction support, ROLLBACK has no effect
-- START TRANSACTION; ... ROLLBACK;  -- silently ignored on MyISAM tables

-- Check which engine a table actually uses
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'shop_db' AND table_name = 'inventory';

3. Locking granularity: row-level vs. table-level

The second fundamental difference lies in lock granularity. InnoDB uses row-level locks, locking only the rows actually affected by a table. Two transactions can change different rows in the same table at the same time without blocking each other. MyISAM, on the other hand, only knows table-level locks. As soon as a write access hits a MyISAM table, the entire table is locked, even for reads, until the write finishes.

In practice this means: under high concurrency with many simultaneous writes, MyISAM scales much worse than InnoDB. An online shop with thousands of concurrent orders would, with MyISAM tables for orders or stock, end up with a queue of blocked writes, because every order locks the whole table. InnoDB avoids this problem through row-level locking combined with MVCC, multiversion concurrency control, which even allows reads to proceed completely without locks.

-- Inspect current locks held by InnoDB transactions
SELECT
    engine_transaction_id,
    object_schema,
    object_name,
    lock_type,
    lock_mode,
    lock_status
FROM performance_schema.data_locks
WHERE object_schema = 'shop_db';

-- MyISAM: no row-level lock info exists, only table-level lock status
SHOW OPEN TABLES WHERE In_use > 0;

-- Demonstrate the blocking effect: a write on MyISAM blocks all readers
-- Session A: LOCK TABLES legacy_stats WRITE;
-- Session B: SELECT * FROM legacy_stats;  -- blocks until Session A unlocks

4. Crash safety and data integrity

Crash safety is where InnoDB and MyISAM differ massively. InnoDB uses write-ahead logging via the redo log: every change is first persisted to the redo log before it lands in the actual data pages. If the server crashes, InnoDB automatically replays the redo log on the next start and restores a consistent state without manual intervention. This property makes InnoDB the only sensible choice for practically any production use.

MyISAM offers no comparable mechanism. A system crash during a write can leave a MyISAM table in a corrupted state that often can only be fixed with REPAIR TABLE or the external tool myisamchk, and not always without data loss. On large tables this repair process can also take a long time, during which the table is unavailable for production use. This property alone disqualifies MyISAM for any application where data loss is not acceptable.

-- Check and repair a MyISAM table after a suspected crash
CHECK TABLE legacy_reports;
REPAIR TABLE legacy_reports;

-- InnoDB requires no manual repair: crash recovery runs automatically
-- at startup by replaying the redo log up to the last checkpoint.
-- Verify recovery happened cleanly via the error log:
-- grep "InnoDB: Starting crash recovery" /var/log/mysql/error.log

5. Full-text search: from MyISAM to InnoDB FULLTEXT

For a long time, full-text search was one of the few domains where MyISAM had a clear technical advantage over InnoDB. Until MySQL 5.5, only MyISAM supported FULLTEXT indexes, which is why many older applications deliberately used MyISAM for search tables, even when the rest of the database already ran on InnoDB. Since MySQL 5.6, InnoDB also supports full-featured FULLTEXT indexes, which has practically eliminated this reason for MyISAM.

Modern InnoDB FULLTEXT indexes offer the same core functionality as their MyISAM counterparts, including boolean-mode search and relevance ranking via MATCH ... AGAINST, combined with the benefits of transactions and row-level locking. For new projects there is no longer any technical reason to fall back on MyISAM for full-text search, even though specialized search solutions like Elasticsearch remain the better choice for more complex requirements.

-- InnoDB fulltext index, no need to fall back to MyISAM anymore
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

-- Boolean mode search with relevance ranking
SELECT id, title,
       MATCH(title, body) AGAINST('+mysql +performance' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('+mysql +performance' IN BOOLEAN MODE)
ORDER BY relevance DESC;

6. Storage overhead and performance characteristics

In pure read benchmarks without concurrency, MyISAM was historically sometimes faster than InnoDB, because MyISAM has to do less internal bookkeeping and keeps no undo logs for MVCC. This advantage has largely disappeared in modern MySQL versions, since InnoDB has been heavily optimized over the years and the buffer pool works more efficiently than MyISAM's simpler key cache. Under realistic workloads with mixed reads and writes, InnoDB is now faster in almost every scenario.

The engines also differ in storage footprint: MyISAM stores data and index files separately and compresses indexes by default, which can mean less space for pure read-only tables. InnoDB tends to need more storage due to its clustered index structure, where the table is physically sorted by the primary key, and due to additional structures like undo logs, but it wins this disadvantage back through faster primary key lookups, since no separate lookup operation is needed.

7. Where MyISAM still shows up today

Despite all its downsides, you still encounter MyISAM in practice in several places. System tables of older MySQL versions historically ran partly on MyISAM, though modern MySQL 8 installations have largely eliminated this. In legacy applications from the 2000s and early 2010s, such as old content management systems or home-built internal tools, MyISAM tables are often still found unchanged, because a migration was never prioritized. Data imports from old backups or migrations from third-party systems can also introduce MyISAM unnoticed into an otherwise modern InnoDB database.

Another often overlooked case is internal statistics or log tables deliberately left on MyISAM because developers in the past assumed transaction safety was dispensable for non-critical data. This can become problematic in practice once these tables are actually used for business-critical reporting. A regular audit of the storage engines in use is therefore part of any solid database maintenance.

-- Find every non-InnoDB table across all schemas, common audit query
SELECT
    table_schema,
    table_name,
    engine,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE engine IS NOT NULL
  AND engine != 'InnoDB'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY size_mb DESC;

8. Migrating from MyISAM to InnoDB

Migrating a MyISAM table to InnoDB is, in the simplest case, a single ALTER TABLE command, but in practice there are several pitfalls. If the table lacks a sensible primary key, which is not uncommon on old MyISAM tables, one should be added before migrating, since InnoDB always needs a primary key internally as a clustered index and otherwise creates a hidden one that slows down access. On large tables, ALTER TABLE ... ENGINE=InnoDB can also take considerable time and temporary storage, since the table is completely rebuilt internally.

For production systems with little maintenance window, using online DDL tools like pt-online-schema-change from the Percona Toolkit or gh-ost is recommended, as they perform the migration without a lengthy table lock. Before any migration it should also be checked whether the application explicitly uses LOCK TABLES syntax with MyISAM-specific behavior, since locking behavior changes fundamentally after migration.

-- Simple migration for small tables
ALTER TABLE legacy_reports ENGINE=InnoDB;

-- Verify the primary key exists before migrating large tables
SHOW KEYS FROM legacy_reports WHERE Key_name = 'PRIMARY';

-- For large production tables, prefer online schema change tools:
-- pt-online-schema-change --alter "ENGINE=InnoDB" \
--   D=shop_db,t=legacy_reports --execute

9. Feature matrix compared directly

The following table summarizes the most important differences between InnoDB and MyISAM compactly.

For practically every modern use case, InnoDB is the right choice. MyISAM remains relevant only in a few niches, for example purely read-only, non-critical reporting tables without any concurrency, and even there the benefits of a unified engine strategy now outweigh it.

10. Summary

The comparison of InnoDB vs. MyISAM shows clearly why InnoDB has been the default choice for over a decade: full transaction support, row-level locking for high concurrency, and automatic crash recovery via the redo log make InnoDB the only sensible engine for practically every production use case. MyISAM lacks all these properties, which is why it no longer plays a supporting role in modern architectures.

Still, knowing the differences pays off, because MyISAM tables regularly show up in legacy systems, often unnoticed inside an otherwise modern InnoDB database. A regular audit via information_schema.tables reliably uncovers such legacy remnants, and a migration with online schema change tools can be carried out even in production environments without significant downtime.

11. FAQ: InnoDB vs. MyISAM