MySQL Storage Engine Showdown: MyISAM vs InnoDB – Which Is Faster?

1. Evolution of MySQL Default Storage Engine

Before MySQL 5.1 the default engine was MyISAM; starting with MySQL 5.5 the default switched to InnoDB.

2. Main Characteristics of MyISAM and InnoDB

MyISAM uses table‑level locking, does not support transactions or foreign keys, and provides full‑text indexing, making it suitable for CMS back‑ends but problematic under high concurrency.

InnoDB offers row‑level locking, ACID‑compliant transactions, foreign‑key support, and (since 5.6.4) full‑text indexing. It is designed for high‑volume workloads with superior CPU efficiency.

Note: InnoDB row locks are not absolute; if MySQL cannot determine an index range, it may lock the whole table.

Example:

update table set num=1 where name like "a%"

3. Performance Test

Official benchmark images show that as CPU cores increase, InnoDB throughput improves, while MyISAM throughput remains flat due to its table‑locking mechanism.

4. Transaction Support

MyISAM is non‑transactional, offering high speed for read‑heavy workloads and full‑text search, but cannot roll back changes. InnoDB provides full transaction safety.

5. Structural Differences

MyISAM stores each table as three files: .frm (definition), .MYD (data), and .MYI (index).

InnoDB uses a shared tablespace and log files; its size is limited only by the operating system (commonly up to 2 GB per file).

6. Table Lock vs Row Lock

MyISAM employs shared read locks and exclusive write locks at the table level. InnoDB uses row‑level locks via index entries; if a query cannot use an index, it falls back to a table lock.

7. Row Count Storage

InnoDB does not store an exact row count, so select count(*) from table requires a full table scan, whereas MyISAM can retrieve a stored count instantly. When a where clause is present, both engines scan rows.

8. Choosing the Right Engine

MyISAM is suitable when:

Frequent COUNT operations are needed.

Writes are infrequent but reads are very frequent.

Transactions are not required.

InnoDB is suitable when:

High reliability or transaction support is required.

Both reads and writes occur frequently.

Large numbers of INSERT or UPDATE statements are expected.

DELETE operations should remove rows individually rather than rebuilding the table.

Features like foreign keys are needed.

Creating a table uses the same DDL; the only difference is the ENGINE (TYPE) parameter.

Other Differences

InnoDB requires a separate index for AUTO_INCREMENT columns; MyISAM can combine it with other columns.

DELETE on InnoDB removes rows one by row; MyISAM rebuilds the table.

LOAD DATA works differently; converting between engines may be necessary for bulk imports.

InnoDB maintains its own buffer pool for caching data and indexes.