If your WordPress database still has MyISAM tables, you’re running on a storage engine that MySQL itself deprecated as the default over a decade ago. InnoDB has been the default since MySQL 5.5, and for good reason, it handles concurrent reads and writes dramatically better than MyISAM, recovers from crashes without data loss, and performs significantly better under the kind of mixed read-write workload that every WordPress site generates. This guide walks through the complete conversion process, from identifying MyISAM tables to tuning InnoDB for optimal WordPress performance.
Why InnoDB Over MyISAM?
MyISAM was designed for an era when databases primarily served read-heavy workloads with minimal concurrent writes. WordPress sites don’t fit that pattern. Every page view that triggers a logged-in session updates the database. Every comment, every WooCommerce order, every form submission, every plugin that tracks analytics, they all write to the database while other visitors are reading from it simultaneously.
The fundamental difference is locking behavior. MyISAM uses table-level locking, which means a single write operation locks the entire table. While one visitor’s comment is being saved to wp_comments, every other query against that table waits. On a busy site, this creates cascading delays that feel like random slowdowns to visitors. InnoDB uses row-level locking, only the specific rows being modified are locked, and all other queries against the same table proceed without waiting.
Beyond locking, InnoDB provides crash recovery through its write-ahead log (WAL). If your MySQL server crashes or loses power during a write operation, InnoDB automatically recovers to a consistent state on restart. MyISAM requires manual table repair after a crash, which can take minutes or hours on large tables and sometimes results in data loss.
| Feature | MyISAM | InnoDB |
|---|---|---|
| Locking granularity | Table-level | Row-level |
| Crash recovery | Manual repair required | Automatic recovery |
| Transaction support | No | Full ACID compliance |
| Foreign key support | No | Yes |
| FULLTEXT search | Yes (original) | Yes (since MySQL 5.6) |
| Concurrent write performance | Poor under load | Excellent under load |
| Data integrity | Lower (no transactions) | Higher (full transactions) |
| Default since MySQL version | Pre-5.5 | 5.5+ (2010) |
Before converting, identify which tables still use MyISAM. Most WordPress sites created on modern MySQL installations will already use InnoDB for all tables. But sites that were created years ago, migrated from older servers, or use plugins that explicitly create MyISAM tables may have a mix of both engines.
Connect to your database via phpMyAdmin, Adminer, or the MySQL CLI and run:
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE = 'MyISAM';If you get results, those tables need conversion. If the query returns empty, you’re already on InnoDB everywhere and can skip to the tuning section to make sure your InnoDB configuration is optimized.
To see a complete picture of all your tables and their engines, run:
SELECT TABLE_NAME, ENGINE, TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_size_mb,
ROUND(INDEX_LENGTH/1024/1024, 2) AS index_size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY DATA_LENGTH DESC;This gives you the table name, engine type, approximate row count, and size of each table. The size information is important because large tables take longer to convert and require more temporary disk space during the conversion process.
Always backup before modifying database structure. The ALTER TABLE operation creates a new copy of the table with the new engine, copies all data, then drops the old table. If anything goes wrong during this process (disk full, server crash, timeout), you need a backup to restore from.
Using WP-CLI (recommended for WordPress sites):
wp db export backup-before-innodb.sqlOr via mysqldump directly:
mysqldump -u username -p database_name > backup-before-innodb.sqlFor large databases (over 1GB), add the –single-transaction flag to avoid locking the entire database during export:
mysqldump -u username -p --single-transaction database_name > backup-before-innodb.sqlVerify your backup file is not empty and has a reasonable size before proceeding. A zero-byte or suspiciously small backup file means something went wrong with the export. Also store the backup somewhere other than the same server, if the server has a disk failure during conversion, a backup on the same disk doesn’t help.
Convert a single table with:
ALTER TABLE wp_posts ENGINE=InnoDB;This works but can be slow on large tables (millions of rows) because MySQL creates a new copy of the table with the new engine, copies all data, then swaps them. During this process, the table is locked for writes. On a live site, this means any operation that needs to write to that table will wait until the conversion completes.
For the core WordPress tables, here’s the approximate conversion time on a typical managed hosting server with SSD storage:
| Table | Typical Size | Conversion Time | Impact During Conversion |
|---|---|---|---|
| wp_posts | 50-500MB | 10-60 seconds | No new posts/pages can be saved |
| wp_postmeta | 100MB-2GB | 30-120 seconds | No metadata writes (affects most operations) |
| wp_options | 5-50MB | 2-10 seconds | Site may be unresponsive |
| wp_comments | 10-100MB | 5-30 seconds | No new comments can be saved |
| wp_usermeta | 10-200MB | 5-60 seconds | No user meta updates |
For small to medium sites, the conversion is fast enough to run during a brief maintenance window. For large sites with millions of posts or WooCommerce orders, consider converting during your lowest-traffic period or putting the site in maintenance mode during the conversion.
Use WP-CLI to convert all MyISAM tables in one command:
wp db query "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND ENGINE = 'MyISAM'" --skip-column-names | wp db queryOr use a PHP script via WP-CLI eval for more control and progress reporting:
wp eval '
global $wpdb;
$tables = $wpdb->get_results("SHOW TABLE STATUS WHERE Engine = \"MyISAM\"");
echo "Found " . count($tables) . " MyISAM tables to convert.\n";
foreach ($tables as $table) {
$start = microtime(true);
$wpdb->query("ALTER TABLE {$table->Name} ENGINE=InnoDB");
$time = round(microtime(true) - $start, 2);
echo "Converted: {$table->Name} ({$time}s)\n";
}
echo "Done. All tables converted to InnoDB.\n";
'The PHP script version is better for large databases because it shows progress as each table is converted and reports the time taken for each conversion. This helps you identify if any particular table is taking unusually long and might need attention.
Converting the engine is only half the optimization. InnoDB’s performance depends heavily on its configuration parameters, and the MySQL defaults are conservative, designed to work on minimal hardware, not to perform optimally on your server.
After converting, optimize your MySQL configuration by adding or modifying these values in your my.cnf or my.ini:
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_instances = 4What each parameter does
innodb_buffer_pool_size is the single most impactful InnoDB tuning parameter. It controls how much RAM InnoDB uses to cache data and indexes. Set it to 70-80% of available RAM on a dedicated database server, or 25-50% on a shared server where MySQL runs alongside PHP and a web server. On a typical managed WordPress host with 2GB RAM, 512M is a reasonable starting point. On a 4GB server, try 1G to 2G.
innodb_log_file_size controls the redo log size. Larger log files mean better write performance (fewer checkpoint flushes) but longer crash recovery time. For WordPress sites, 64M to 256M is the sweet spot. WooCommerce sites with heavy order volume benefit from the larger end of this range.
innodb_flush_log_at_trx_commit controls how often InnoDB flushes its log to disk. The default value of 1 is safest (flush on every transaction commit) but slowest. Value 2 flushes to the OS cache on every commit but only writes to disk once per second, this gives significantly better write performance with minimal risk (you could lose up to 1 second of transactions in a crash, but the OS crash and MySQL crash are different events). For most WordPress sites, 2 is the right balance of safety and performance.
innodb_flush_method = O_DIRECT tells InnoDB to bypass the OS file cache when reading and writing data files. This avoids double-buffering (data sitting in both the InnoDB buffer pool and the OS cache) and is recommended on Linux systems with sufficient innodb_buffer_pool_size.
innodb_file_per_table = 1 stores each table in its own file rather than a single shared tablespace file. This makes it easier to reclaim disk space after deleting data and allows per-table backup and restore.
After changing these settings, restart MySQL for them to take effect. Monitor your server’s performance for a few days and adjust if needed, particularly the buffer pool size, which may need tuning based on your actual workload patterns.
- FULLTEXT indexes: MyISAM supported FULLTEXT indexes before InnoDB did. If you have FULLTEXT indexes on MyISAM tables, they’ll be automatically converted. InnoDB has supported FULLTEXT since MySQL 5.6, so this shouldn’t be an issue on modern servers. If you’re on MySQL 5.5 or older (check with
mysql --version), you should upgrade MySQL before converting tables with FULLTEXT indexes. - Table size increase: InnoDB tables are typically 1.5-2x larger on disk than MyISAM because InnoDB stores more metadata for crash recovery, row-level locking, and transaction support. Ensure you have enough disk space before starting, check your current database size and verify you have at least 3x that amount free (you need space for the original table, the new InnoDB copy, and working space).
- Memory usage: InnoDB uses more RAM through the buffer pool. If your server has limited memory, start with a smaller
innodb_buffer_pool_sizeand increase gradually while monitoring for out-of-memory issues. - COUNT(*) performance change: MyISAM stores an internal row count, so
SELECT COUNT(*) FROM tablereturns instantly. InnoDB doesn’t store this and must scan the table or an index, which is slower. This rarely matters for WordPress but can affect plugins that frequently count all rows in large tables. - Temporary table behavior: If your site or plugins create temporary MyISAM tables explicitly, the conversion doesn’t affect them. Only permanent tables in your database are converted by ALTER TABLE. Temporary tables are created fresh on each use and destroyed afterward.
If you’re on a managed WordPress host like Kinsta, WP Engine, Cloudways, or SiteGround, your situation may be different. Most managed hosts configure MySQL with InnoDB as the default engine, so tables created through normal WordPress operations should already be InnoDB. However, there are scenarios where MyISAM tables can still appear on managed hosting.
First, if you migrated your site from an older server or a different hosting provider, the tables retain whatever engine they had on the original server. A site that was created on a server running MySQL 5.0 in 2008 and has been migrated through three different hosts over the years may still have MyISAM tables from the original installation. Second, some older plugins explicitly create tables with MyISAM engine specifications in their CREATE TABLE statements, overriding the server default. Third, database imports from mysqldump files preserve the original engine type unless you specifically filter them during import.
On managed hosts where you don’t have direct access to my.cnf for InnoDB tuning, the conversion itself is still worth doing. The managed host has already optimized the InnoDB configuration for their infrastructure, so you get the benefit of their tuning automatically once your tables are on InnoDB. Most managed hosts provide WP-CLI access, database access through phpMyAdmin, or a built-in database management tool that lets you run the ALTER TABLE commands. If you’re unsure, contact your host’s support team, they’ll typically handle the conversion for you or confirm that all tables are already on InnoDB.
WooCommerce databases present unique challenges during MyISAM to InnoDB conversion because they tend to be significantly larger than standard WordPress databases. A WooCommerce store with 50,000 orders can have a wp_postmeta table with millions of rows and multiple gigabytes of data. Converting a table this large requires careful planning.
The primary concern is downtime. Converting a 2GB wp_postmeta table can take 5-15 minutes depending on server hardware, during which writes to that table are blocked. For an active WooCommerce store, this means no orders can be processed during the conversion. Schedule the conversion during your absolute lowest traffic period, typically between 2 AM and 5 AM in your primary customer timezone.
Before starting, verify you have sufficient disk space. The conversion process temporarily requires roughly double the table size in free disk space because MySQL creates a complete copy of the table before swapping it. A 2GB table needs approximately 4GB of free space during conversion. Check your available disk space before starting and ensure you have at least 3x the size of your largest table available.
For very large databases where even brief downtime is unacceptable, consider using Percona’s pt-online-schema-change tool. This tool performs the ALTER TABLE operation without locking the table by creating a shadow copy, applying changes to the copy while syncing new writes via triggers, then atomically swapping the tables. The command looks like this: pt-online-schema-change –alter “ENGINE=InnoDB” D=your_database,t=wp_postmeta –execute. This approach adds complexity but eliminates the write lock during conversion.
After converting all existing tables, prevent new MyISAM tables from being created. Add this line to your MySQL configuration to ensure the server default is always InnoDB:
default_storage_engine = InnoDBThis setting ensures that any CREATE TABLE statement that doesn’t explicitly specify an engine will use InnoDB. However, it doesn’t prevent plugins from explicitly creating MyISAM tables with ENGINE=MyISAM in their SQL statements. If you discover a plugin creating MyISAM tables, check if a newer version of the plugin has fixed this, or contact the plugin developer, specifying MyISAM in 2026 is a code smell that suggests the plugin may have other outdated practices worth investigating.
You can also set up a simple monitoring check that alerts you if any MyISAM tables appear in your database. Add a WP-CLI command to your server’s daily cron that checks for MyISAM tables and sends an alert if any are found. This catches both plugin-created tables and any tables that might revert during a problematic database restore or migration.
After converting and tuning, verify that InnoDB is performing well. The most important metric is the buffer pool hit ratio, the percentage of read requests served from the buffer pool cache rather than from disk.
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';Calculate the hit ratio: Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads). A healthy WordPress site should show a hit ratio above 99%. If it’s below 95%, your innodb_buffer_pool_size is too small and the database is reading from disk too frequently.
Other useful monitoring queries include checking for long-running transactions that might be holding locks:
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;And checking the overall InnoDB status for any warnings or performance issues:
SHOW ENGINE INNODB STATUS;If you’re using a managed WordPress host like Cloudways, Kinsta, or WP Engine, many of these MySQL tuning parameters are already optimized and may not be changeable through your hosting panel. In that case, focus on the conversion itself, the hosting provider has likely already tuned InnoDB for WordPress workloads.
Several WordPress core tables benefit especially from InnoDB’s row-level locking. The wp_options table is the biggest beneficiary because WordPress reads from it on every single page load (autoloaded options) and many plugins write to it frequently (transients, settings updates, cron locks). Under MyISAM, a plugin writing a transient to wp_options would briefly lock the entire table, blocking every concurrent page load that needs to read options. Under InnoDB, that write only locks the single row being modified.
The wp_postmeta table is another major beneficiary on WooCommerce sites. WooCommerce stores order data, product data, and customer data in postmeta, and a busy store generates concurrent reads and writes to this table constantly. MyISAM table-level locking on wp_postmeta is one of the most common causes of mysterious slowdowns on WooCommerce stores, and many store owners never trace it to the storage engine because the symptoms look like generic “server is slow” rather than a specific bottleneck.
After conversion, you should also run the WordPress database optimization to clean up overhead from the conversion process:
wp db optimizeThis runs OPTIMIZE TABLE on each table, which reclaims unused space and defragments the data files. For InnoDB tables with innodb_file_per_table enabled, this can significantly reduce disk usage after the conversion.
After converting, verify all tables are now InnoDB:
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';Every table should show InnoDB in the ENGINE column. If any tables still show MyISAM, check the conversion log output for errors, the most common cause is insufficient disk space or permissions issues.
Database engine conversion is one piece of a comprehensive WordPress performance strategy. For object caching that reduces database load even further, see our Redis vs Memcached comparison with real WordPress benchmarks. And if your WordPress admin dashboard feels sluggish, the Heartbeat API optimization guide addresses one of the most common causes of admin-side performance issues.
Yes, almost always. The only edge case where MyISAM might still be preferred is read-only tables with heavy FULLTEXT search and zero concurrent writes, a scenario that essentially never occurs on a WordPress site. For a typical WordPress site with concurrent reads and writes (which describes every WordPress site that has active users, comments, or any form of dynamic content), InnoDB is unequivocally better in every measurable dimension: performance under concurrent load, data integrity, crash recovery, and compatibility with modern MySQL features.
The conversion process itself is straightforward and low-risk when you follow the steps above: backup first, convert during low-traffic hours, verify afterward, and tune your InnoDB configuration. Most WordPress sites complete the entire process in under 15 minutes, and the performance improvement, particularly on sites running WooCommerce or any plugin that writes to the database frequently, is immediately noticeable.
If you’ve been putting off this conversion because it seemed risky or complicated, the reality is that the risk of staying on MyISAM is higher than the risk of converting. Every day your tables run on MyISAM is another day where a server crash could corrupt data that InnoDB would have recovered automatically, and another day where table-level locks are silently degrading your site’s performance under concurrent load. Back up your database, run the conversion, verify the results, and move on to the many other WordPress performance optimizations that will make a real difference for your visitors.
database cleanup database optimization
Last modified: March 11, 2026
