Migrate MyISAM to InnoDB: Why It's Mandatory and How to Do It Safely

MyISAM was MySQL's default storage engine through version 5.5 (2010). InnoDB has been the standard ever since - and not by accident. MyISAM has three limitations that disqualify it from any production site: table-level locking (every UPDATE or INSERT locks the whole table, not just the row), no transactions (a query that fails midway leaves half-written data), and slow crash recovery (minutes to hours after a server reboot, versus seconds with InnoDB).

Why this matters

Inside WordPress, the table lock is the most felt symptom. Picture a WooCommerce store with ten shoppers checking out in parallel - every INSERT into wp_postmeta locks the entire table for a few milliseconds. Anyone trying to read from it (every product page!) waits. Load that InnoDB shrugs off can topple a MyISAM site. Backups that issue FLUSH TABLES WITH READ LOCK create long downtime windows on large stores.

The lack of transactions is even more dangerous. With MyISAM, if PHP dies mid-way through a multi-table operation (creating a subscription that touches users + usermeta + posts), you keep inconsistent rows. WooCommerce, EDD, BuddyPress, and any multi-table workflow suffers. WordPress core itself defaults to InnoDB since WordPress 4.0 (2014).

How to detect

The standard diagnostic query lists every table and its engine:

SELECT table_name, engine, table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY engine, data_mb DESC;

Any row with engine = 'MyISAM' needs migrating. A common pattern is wp_postmeta or wp_options stuck on MyISAM after a long-ago upgrade that never converted them.

How to fix

Step one: back up. Never migrate without a backup. Use mysqldump --single-transaction --routines DBNAME > backup.sql. Step two: generate the ALTER statements:

SELECT CONCAT('ALTER TABLE ', table_schema, '.',
              table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = 'YOUR_DB_NAME' AND engine = 'MyISAM';

Paste the output back into SQL and execute. Each ALTER copies the table into the new engine and swaps the original - heavy work that can take minutes on large tables. With WP-CLI: wp db query "ALTER TABLE wp_options ENGINE=InnoDB;" is faster and easier to script.

Step three: make sure future tables default to InnoDB by editing my.cnf:

[mysqld]
default_storage_engine=InnoDB
innodb_buffer_pool_size=512M

Restart MySQL. On shared hosting where my.cnf is unreachable, ask support to set it.

Common mistakes

Do not migrate tables with FULLTEXT indexes on MySQL older than 5.6 - early InnoDB did not support them and the ALTER will fail. MySQL 5.6+ handles it natively. Mistake two: running ALTER in parallel on several large tables - the disk will buckle. Migrate one at a time. Mistake three: forgetting innodb_buffer_pool_size after the switch. InnoDB depends on the buffer pool for read performance, and the 128MB default is too small for most production sites. Aim for 50-70% of available RAM.

Verifying the fix

Run the diagnostic query again - no MyISAM rows should remain. On staging, run a load test simulating concurrent writes; the site should handle it more smoothly than before. WooCommerce shops should checkout cleanly from three browsers at once with no perceived lag. Inspect /var/lib/mysql - InnoDB stores data in ibdata1 or per-table .ibd files, and you should see the new files appear after the migration.