InnoDB vs MyISAM

Also Known As

TL;DR

Explanation

InnoDB has been MySQL's default since 5.5. It supports ACID transactions, row-level locking (better concurrency), foreign key constraints, crash recovery, and MVCC. MyISAM offers faster full-table reads and has slightly smaller on-disk storage but lacks transactions, crashes inconsistently, and uses table-level locking that serialises all writes. New tables should always use InnoDB. MyISAM FULLTEXT search support has been superseded by InnoDB FULLTEXT (available since MySQL 5.6).

Common Misconception

Why It Matters

Common Mistakes

• Creating tables without ENGINE=InnoDB on older MySQL installations that default to MyISAM.
• Using MyISAM tables in a schema that relies on foreign keys — MyISAM silently ignores FK constraints.
• Not converting existing MyISAM tables when adding transactions to an application.

Avoid When

• Never use MyISAM for tables that participate in transactions or have foreign key constraints.
• Do not use MyISAM for any table that receives concurrent writes — table-level locking serialises all writes.

When To Use

• Always use InnoDB — it is the default and correct choice for all new tables.
• Migrate existing MyISAM tables to InnoDB with ALTER TABLE ... ENGINE=InnoDB.

Code Examples

-- MyISAM: no transactions, no FK, table-level lock
CREATE TABLE orders (...) ENGINE=MyISAM;

-- Always explicit ENGINE=InnoDB
CREATE TABLE orders (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    total      DECIMAL(10,2) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

Tags

Related Terms