MyISAM was MySQL’s default storage engine until MySQL 5.5. Many older applications, especially WordPress sites created years ago, still have MyISAM tables. InnoDB is better in almost every way for modern workloads, and converting is usually a single SQL statement per table. Here’s how to do it safely.

Why convert

MyISAM InnoDB
Crash safety No: tables can be corrupted by crashes Yes: redo log recovery
Locking Whole-table locks on writes Row-level locks
Transactions No Yes (ACID)
Foreign keys No Yes
Caching Indexes only (key buffer) Data and indexes (buffer pool)
Hot backups Need locks --single-transaction, XtraBackup
Development Legacy Actively developed

On a busy site, MyISAM’s table locks cause queries to pile up behind each write. Corrupted MyISAM tables after a crash are a classic support ticket. See repairing crashed MySQL tables.

1. Find MyISAM tables

SELECT table_schema, table_name, ROUND((data_length + index_length)/1024/1024, 1) AS size_mb
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY size_mb DESC;

Leave system schemas alone. In MySQL 8, the mysql system tables are already InnoDB.

2. Back up first

mysqldump --routines --triggers --events mydb | gzip > mydb-before-innodb.sql.gz

A MyISAM dump can’t use --single-transaction for consistency, so run it during a quiet period. See mysqldump backup and restore.

3. Check for blockers

  • FULLTEXT indexes: supported by InnoDB since MySQL 5.6, so this is fine on any current version.
  • Very long rows or many columns: InnoDB’s row size limits are stricter. If conversion fails with “Row size too large”, use ROW_FORMAT=DYNAMIC (the default in modern versions) or change some columns to TEXT.
  • Applications relying on COUNT(*) being instant: MyISAM stores the row count, while InnoDB counts rows. Large counts become slower.
  • Disk space: InnoDB tables are often larger than MyISAM, and conversion needs space for a temporary copy of each table.

4. Convert

One table:

ALTER TABLE mydb.wp_options ENGINE=InnoDB;

ALTER TABLE … ENGINE rebuilds the table, and locks writes to it during the copy. Small tables take a fraction of a second; multi-gigabyte tables can take minutes. Convert large tables during a maintenance window, or use an online schema change tool (pt-online-schema-change, gh-ost) to avoid blocking.

Generate statements for a whole database:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema = 'mydb';
mysql -N -e "SELECT CONCAT('ALTER TABLE \`', table_schema, '\`.\`', table_name, '\` ENGINE=InnoDB;') FROM information_schema.tables WHERE engine='MyISAM' AND table_schema='mydb'" > convert.sql
mysql < convert.sql

WordPress notes

Old WordPress installs often have MyISAM core tables (wp_posts, wp_options, wp_comments) and plugin tables. Converting all of them to InnoDB is safe and recommended. Plugins like WP-CLI’s wp db query can run the statements, or use phpMyAdmin’s operations tab table by table. Put the site in maintenance mode for large tables. See WordPress database optimization.

5. Tune for InnoDB afterwards

MyISAM relies on key_buffer_size and the OS cache. InnoDB relies on the buffer pool. After converting, shrink key_buffer_size (e.g. 16–32M, still used for temporary tables in some versions), and give InnoDB the memory:

[mysqld]
innodb_buffer_pool_size = 1G      # size to your RAM and data
key_buffer_size         = 32M

See MySQL InnoDB buffer pool and MySQL configuration files.

6. Make InnoDB the default

It’s the default since MySQL 5.5, but check that no old config overrides it:

SHOW VARIABLES LIKE 'default_storage_engine';

Also check that your application’s install scripts don’t create MyISAM tables explicitly (ENGINE=MyISAM in their SQL).

Verify

SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema = 'mydb' GROUP BY engine;
CHECK TABLE mydb.wp_options;

Then watch the application, slow query log, and server load for a few days. Write-heavy workloads usually see the biggest improvement. See the MySQL slow query log.

Quick reference

Task Command
Find MyISAM tables Query information_schema.tables WHERE engine='MyISAM'
Convert one table ALTER TABLE t ENGINE=InnoDB;
Convert many Generate ALTER statements with CONCAT
Large tables online pt-online-schema-change, gh-ost
Tune afterwards Raise innodb_buffer_pool_size, shrink key_buffer_size

Related: MySQL performance tuning and MariaDB vs MySQL.

  • MySQL
  • Database
  • Performance