InnoDB vs MyISAM
debt(d5/e3/b5/t7)
Closest to 'specialist tool catches it' (d5). The detection_hints list semgrep as the tool, with a code_pattern of ENGINE=MyISAM in CREATE TABLE or SHOW TABLE STATUS. Semgrep is a specialist static analysis tool that requires deliberate configuration, not a default linter or compiler error, placing this squarely at d5.
Closest to 'simple parameterised fix' (e3). The quick_fix is to specify ENGINE=InnoDB in CREATE TABLE or set innodb as default_storage_engine in config. Migrating existing tables requires running ALTER TABLE ... ENGINE=InnoDB per table, which is a small repeatable pattern across potentially multiple tables but not a deep architectural refactor — closest to e3, nudged to e3 as the fix is straightforward even if applied to multiple tables.
Closest to 'persistent productivity tax' (b5). MyISAM's lack of transactions and table-level locking affects any concurrent write workload and any code that relies on foreign keys or transactional integrity. The burden applies broadly across web and CLI contexts (per applies_to), meaning many future features touching the DB are shaped by this choice, though it doesn't fully define the system's architecture.
Closest to 'serious trap' (t7). The misconception field explicitly states that developers believe MyISAM is faster, when InnoDB is actually faster for typical concurrent OLTP workloads. Additionally, MyISAM silently ignores FK constraints (noted in common_mistakes), meaning behaviour contradicts what a developer familiar with relational databases would expect — a silent, dangerous divergence from standard relational semantics that contradicts how foreign keys work elsewhere.
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;