MySQL MyISAM to InnoDB Conversion: Why and How

MyISAM was the default MySQL storage engine until 5.5 (released 2010). It is still around because some hosting providers don't bother changing the default, and because old CREATE TABLE statements without an ENGINE= clause used to land on MyISAM. The case for converting is unambiguous: MyISAM has no transactions, no foreign-key enforcement, no crash-safety (a hard restart can leave a table in a state that needs REPAIR TABLE), and is excluded from every major MySQL feature added since 5.5. InnoDB is the only engine the MySQL team is actively developing, the only one that supports INSTANT schema changes, and the only one that survives a kernel panic without manual intervention. Below is the conversion: one query to find every MyISAM table you own, the ALTER TABLE to convert each one, what to do with FULLTEXT indexes (the historical reason to stay on MyISAM), and the size and time tradeoffs.

How do I convert a MyISAM table to InnoDB?

The conversion is a single SQL statement per table: ALTER TABLE table_name ENGINE=InnoDB;. MySQL reads the table data, writes it back in InnoDB format with the same columns and indexes, then drops the old MyISAM files. To find every MyISAM table in a database, query information_schema.tables filtered by engine='MyISAM'. To convert all of them at once, generate ALTER TABLE statements from that query and execute the result. Before converting, check three things: FULLTEXT indexes were MyISAM-only until MySQL 5.6, so verify your version supports InnoDB FULLTEXT before the conversion (5.6+ is fine). Foreign keys were ignored on MyISAM, so any REFERENCES clauses in the schema will now be enforced and may reveal orphan rows. Disk space doubles temporarily during conversion. The conversion is online (the table is readable during ALTER) on MySQL 5.6+ with InnoDB.

Jump to:

• Why convert at all
• Find every MyISAM table
• The conversion query
• Bulk convert every table in a database
• Handling FULLTEXT indexes
• Foreign keys are now enforced
• Performance differences
• Disk space during conversion
• Verification
• FAQ

Why convert at all

The honest list. MyISAM has been the wrong choice for production workloads since 2010, and the gap has only widened.

The historical reasons to choose MyISAM (faster COUNT(*), smaller on-disk size, FULLTEXT) no longer outweigh the costs. MySQL 8.0.14 parallelizes the InnoDB clustered-index scan for unfiltered COUNT(*), which makes it much faster (it is still a scan, not an O(1) cached count). FULLTEXT works on both. The disk-size delta on a typical schema is maybe 10-15% and is more than offset by InnoDB's compressed row formats.

If you have a WordPress site running on MyISAM (common on shared hosts that haven't updated defaults in a decade), the conversion has measurable effects on performance under concurrent writes, on the ability to use modern caching plugins that rely on transactions, and on backup reliability.

Find every MyISAM table

The diagnostic query. Run this first, before any conversion:

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

This lists every user MyISAM table with size estimates. Pin the output; you'll use it to track progress through the conversion and to compare sizes before/after.

The conversion query

For a single table:

ALTER TABLE my_table ENGINE=InnoDB;

That's it. MySQL handles the rest: locks the table for the duration (with row-level reads still permitted on 5.6+ with ALGORITHM=INPLACE), copies rows into a new InnoDB table, swaps the file pointers, drops the MyISAM table.

Time-wise, a 1 GB MyISAM table converts in roughly 30-90 seconds on a modern SSD-backed server. A 50 GB table is 30-60 minutes. The duration scales with the total table size (data + indexes), not row count.

To explicitly request the online algorithm (5.6+):

ALTER TABLE my_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

If that fails (some older 5.6 builds, or schemas with FULLTEXT being added concurrently), fall back to the default ALGORITHM=COPY which takes a metadata lock for the whole operation.

Bulk convert every table in a database

The trick: have MySQL generate the SQL for you, then execute the generated SQL. Two-step procedure.

Step 1: generate the ALTER statements:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sql_stmt
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY (data_length + index_length) ASC;

The ORDER BY ASC (smallest first) is intentional: short ALTERs build confidence; if a small table fails, you know early rather than mid-way through a 50 GB conversion.

Step 2: capture and execute. From the command line, redirect the query to a file and run it back:

# 1. Generate the script
mysql -u root -p -N -e "
    SELECT CONCAT('ALTER TABLE \`', table_schema, '\`.\`', table_name, '\` ENGINE=InnoDB;')
    FROM information_schema.tables
    WHERE engine = 'MyISAM'
      AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
    ORDER BY (data_length + index_length) ASC;
" > convert-to-innodb.sql

# 2. Inspect the file — sanity check the count and the table list
wc -l convert-to-innodb.sql
head convert-to-innodb.sql

# 3. Execute it (logs each ALTER and how long it took)
mysql -u root -p --verbose < convert-to-innodb.sql 2>&1 | tee convert-to-innodb.log

The --verbose flag makes mysql print each statement before executing, so the log file gives you a per-table breakdown of time.

For a single specific database, add AND table_schema = 'mydb' to the WHERE clause.

Handling FULLTEXT indexes

FULLTEXT indexes were MyISAM-only through MySQL 5.5. InnoDB gained FULLTEXT support in 5.6. If your MySQL version is 5.6 or higher, MyISAM FULLTEXT indexes are converted directly to InnoDB FULLTEXT during ALTER TABLE ... ENGINE=InnoDB. No special steps needed.

If you somehow still run MySQL 5.5 (which has been EOL since 2018), you must drop the FULLTEXT index before conversion, convert the engine, then choose whether to:

• Recreate the FULLTEXT on InnoDB after upgrading to 5.6+,
• Move FULLTEXT search out of MySQL entirely (Elasticsearch, MeiliSearch, Postgres tsvector, or ElasticPress for WordPress),
• Use MySQL 8.0's improved FULLTEXT (still on InnoDB) which has better tokenization.

For WordPress sites that rely on FULLTEXT for the search box, InnoDB FULLTEXT performs adequately on databases up to a few million posts. Beyond that, an external search engine is usually the right answer.

Foreign keys are now enforced

MyISAM ignored FOREIGN KEY clauses in CREATE TABLE. The syntax parsed and was stored, but no constraint was actually enforced. After conversion to InnoDB, these constraints become real. Two failure modes:

Orphan rows. If a child table has rows whose foreign-key value doesn't match any row in the parent, the constraint is violated at the moment InnoDB enforces it. Symptoms: the ALTER TABLE for the child fails with a Cannot add foreign key constraint error.

Find orphans before converting:

-- Example: posts.user_id should reference users.id
SELECT p.user_id
FROM posts p
LEFT JOIN users u ON u.id = p.user_id
WHERE u.id IS NULL
  AND p.user_id IS NOT NULL;

Fix orphans (delete, or re-point to a placeholder user) before the conversion runs.

Circular dependencies. If two tables reference each other, you can't ALTER either one first without breaking the reference. Convert both with foreign keys temporarily disabled:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE parent ENGINE=InnoDB;
ALTER TABLE child  ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS = 1;

The constraints are re-evaluated when FOREIGN_KEY_CHECKS=1 is re-enabled. Orphan-row violations will still surface at that point.

Performance differences

The numbers vary by workload. General guidance from production conversions:

For a real-world WordPress site moving from MyISAM to InnoDB, expect concurrent-write throughput to improve by 3-10x. Read latency under load improves a similar amount. Crash recovery becomes routine instead of an emergency.

Disk space during conversion

ALTER TABLE ... ENGINE=InnoDB is implemented as: create a new InnoDB table, copy rows, drop the old MyISAM. During the copy, both the source MyISAM and the destination InnoDB exist on disk simultaneously. Free-disk requirement is roughly:

free space >= size of the largest MyISAM table being converted + 10% margin

For the bulk-convert script, you don't need 2x of the total — only 2x of the largest single table, since tables are converted one at a time. But check this before starting:

-- Find the largest MyISAM table
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.tables
WHERE engine = 'MyISAM'
ORDER BY total_gb DESC
LIMIT 5;

If the largest table is 100 GB, you need at least 110 GB free.

Post-conversion, InnoDB tables are typically 10-30% larger than the MyISAM equivalent on disk. The two-byte primary-key overhead per row and the clustered-index structure account for most of the difference. The cost is real but small.

Verification

After the bulk conversion:

-- Should return zero rows
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

-- Engine distribution
SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
GROUP BY engine;

-- Should not have any obvious row-count discrepancies
-- (run before and after — InnoDB row counts in information_schema are estimates,
--  so for exact counts query the table directly)
SELECT COUNT(*) FROM critical_table;

The mysql.user, mysql.db, and the rest of the mysql system schema tables are intentionally still MyISAM through 5.7 and become InnoDB in 8.0. Don't convert those manually; they're managed by the upgrade process.

What to do next

After the conversion:

• How to Migrate from MySQL 5.7 to 8.0 — the version upgrade that requires InnoDB anyway.
• utf8 to utf8mb4 Migration in MySQL — the next character-set chore.
• MySQL Cheat Sheet — for the post-conversion management commands.
• How to Find Duplicate Rows in MySQL — using the new transactional consistency to catch what foreign keys would have caught.
• How to Find Rows in One MySQL Table Not in Another — for the orphan-row detection pattern above.

External references:

• MySQL Reference Manual: Converting Tables from MyISAM to InnoDB
• MySQL Reference Manual: The InnoDB Storage Engine

FAQ