# MyISAM vs InnoDB: Fundamental Differences Between MySQL Storage Engines

> Explore MyISAM vs InnoDB differences. Understand table vs row locking, transaction support, and crash safety to optimize your MySQL performance.

- Repository: [CyC2018/CS-Notes](https://github.com/CyC2018/CS-Notes)
- Tags: deep-dive
- Published: 2026-02-24

---

**MyISAM and InnoDB differ fundamentally in that MyISAM uses table-level locking without transaction support, while InnoDB provides ACID-compliant transactions, row-level locking, and crash-safe recovery through MVCC architecture.**

When architecting MySQL databases, choosing between storage engines critically impacts data integrity and concurrency. According to the CyC2018/CS-Notes repository's detailed analysis in [`notes/MySQL.md`](https://github.com/CyC2018/CS-Notes/blob/main/notes/MySQL.md), these engines differ dramatically in transaction support, locking granularity, and recovery mechanisms despite both handling tabular data storage.

## Transaction Support and ACID Compliance

MyISAM does not support transactions. Every statement executes in autocommit mode, meaning changes are permanent immediately without support for `COMMIT` or `ROLLBACK` operations. This architectural limitation makes MyISAM unsuitable for applications requiring atomic operations or consistency guarantees across multiple statements.

InnoDB is fully ACID-compliant, implementing transaction control through `BEGIN`, `COMMIT`, and `ROLLBACK` statements as documented in [`notes/MySQL.md`](https://github.com/CyC2018/CS-Notes/blob/main/notes/MySQL.md) (lines 61-71). This ensures that groups of operations succeed or fail atomically, maintaining data integrity even during system failures or concurrent access patterns.

## Locking Mechanisms and Concurrency

### Table-Level vs Row-Level Locking

MyISAM employs **table-level locks** for both read and write operations. When a write lock is acquired, it blocks all other read and write operations on the entire table, creating significant concurrency bottlenecks under heavy write loads (lines 75-84).

InnoDB implements **row-level locking** combined with gap locks, allowing multiple transactions to modify different rows simultaneously without blocking. This fine-grained locking model enables significantly higher concurrency for write-intensive workloads and mixed read-write scenarios.

### MVCC and Non-Blocking Reads

InnoDB utilizes **Multi-Version Concurrency Control (MVCC)** to provide consistent reads without locking rows. As implemented in the CS-Notes documentation (lines 67-71), MVCC maintains multiple versions of data, allowing readers to access snapshot versions without blocking writers. MyISAM lacks this capability entirely, requiring readers to wait for write locks to release, effectively serializing access during updates.

## Storage Architecture and Indexing

### Clustered vs Non-Clustered Indexes

InnoDB implements a **clustered primary key** architecture where primary key values are stored directly in the leaf nodes of the B+Tree index. This design eliminates the need for secondary lookups, making primary-key lookups O(log N) operations (lines 89-91).

MyISAM stores data and indexes in separate files. The index file contains pointers to physical record offsets on disk, requiring a secondary lookup to retrieve actual row data. While this makes range scans efficient for static datasets, it penalizes random access patterns and increases I/O operations.

### Buffer Pool vs Key Cache

InnoDB caches both data and indexes in the **InnoDB Buffer Pool**, reducing disk I/O for frequently accessed rows. MyISAM only caches indexes in the key cache, reading data blocks from disk for every query regardless of access frequency, which limits performance on large datasets.

## Crash Recovery and Data Integrity

### Automatic Recovery Mechanisms

InnoDB maintains **redo logs** and **undo logs** to ensure crash-safe recovery. During startup, InnoDB automatically performs recovery by replaying committed transactions from the redo log and rolling back uncommitted changes using undo logs (lines 63-71). This guarantees that committed data survives crashes while incomplete transactions are properly discarded.

MyISAM lacks transactional logging infrastructure. After a crash, tables may require manual repair using `CHECK TABLE` and `REPAIR TABLE` commands, carrying higher risk of unrecoverable corruption and data loss.

### Foreign Key Constraints

InnoDB natively enforces **foreign key constraints**, maintaining referential integrity between tables through cascading updates and deletes. MyISAM parses but ignores foreign key syntax, providing no enforcement of relational constraints (lines 89-97).

## Practical Code Examples

### Creating Tables with Different Engines

```sql
-- MyISAM table creation
CREATE TABLE logs_myisam (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM;

-- InnoDB table creation
CREATE TABLE logs_innodb (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

```

### Transaction Behavior Demonstration

```sql
-- InnoDB: Transaction support
START TRANSACTION;
INSERT INTO logs_innodb (message) VALUES ('Critical system error');
INSERT INTO logs_innodb (message) VALUES ('Backup completed');
ROLLBACK; -- Both inserts are discarded

-- MyISAM: Statements commit immediately regardless of transaction syntax
START TRANSACTION;
INSERT INTO logs_myisam (message) VALUES ('This persists immediately');
ROLLBACK; -- Has no effect; data remains committed

```

### Locking Granularity Examples

```sql
-- InnoDB: Row-level locking allows concurrent access
BEGIN;
SELECT * FROM logs_innodb WHERE id = 1 FOR UPDATE;
-- Session 2 can simultaneously lock row 2 without waiting
COMMIT;

-- MyISAM: Table-level locking blocks entire table
LOCK TABLES logs_myisam WRITE;
-- All other sessions blocked until UNLOCK TABLES is executed
UNLOCK TABLES;

```

### Foreign Key Enforcement

```sql
-- Parent table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

-- Child table with constraint
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE CASCADE
) ENGINE=InnoDB; -- Foreign keys require InnoDB

```

## Summary

- **Transactions**: InnoDB supports ACID transactions with rollback capabilities; MyISAM executes all statements as autocommit with no rollback support.
- **Locking**: InnoDB implements fine-grained row-level locks; MyISAM uses coarse table-level locks that block concurrent access.
- **Recovery**: InnoDB provides automatic crash recovery via redo and undo logs; MyISAM requires manual repair and risks corruption.
- **Architecture**: InnoDB uses clustered indexes for O(log N) primary key lookups; MyISAM maintains separate index and data files requiring double lookups.
- **Referential Integrity**: InnoDB enforces foreign key constraints; MyISAM ignores foreign key definitions.

## Frequently Asked Questions

### Which storage engine is faster for read-heavy workloads?

MyISAM historically provided faster sequential read performance for static, rarely-updated datasets through compressed table formats. However, with the **InnoDB Buffer Pool** caching both data and indexes in memory, modern InnoDB implementations typically match or exceed MyISAM performance for most read patterns while maintaining superior concurrency for mixed workloads.

### Can I convert an existing MyISAM table to InnoDB?

Yes, execute `ALTER TABLE table_name ENGINE=InnoDB;` to convert the storage engine. Before converting production tables, verify that your application does not depend on MyISAM-specific behaviors like table-level locking semantics or full-text search limitations (addressed in MySQL 5.6+ for InnoDB), and test transaction handling logic thoroughly.

### Why does MyISAM not support foreign keys?

MyISAM's architecture predates MySQL's foreign key implementation and lacks the transactional infrastructure necessary to enforce referential integrity constraints. While the parser accepts foreign key syntax for compatibility, MyISAM does not create the underlying constraint indexes or enforce cascading rules, risking orphan records and inconsistent relationships.

### How do crash recovery mechanisms differ between the engines?

InnoDB uses **redo logs** to replay committed transactions and **undo logs** to remove uncommitted changes during automatic recovery, ensuring data consistency after crashes (lines 63-71). MyISAM relies on manual `CHECK TABLE` and `REPAIR TABLE` operations that scan and potentially truncate corrupted rows, offering no guarantee of transaction atomicity or data completeness.