Convert MyISAM to InnoDB in MySQL: ALTER TABLE ENGINE

Many old databases still contain MyISAM tables. Converting them to InnoDB gives you transactions, row locking, crash safety and foreign keys. The conversion itself is one statement, but the planning matters.

1. Find the tables to convert

query.sql

SQL

CREATE TABLE legacy_users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(80) NOT NULL, bio TEXT) ENGINE = MyISAM;
CREATE TABLE legacy_posts (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, title VARCHAR(100) NOT NULL, FULLTEXT KEY ft_title (title)) ENGINE = MyISAM;
INSERT INTO legacy_users (email, bio) VALUES ('a@example.com', 'hello'), ('b@example.com', 'world');
INSERT INTO legacy_posts (user_id, title) VALUES (1, 'First post'), (2, 'Second post');

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'learn' AND engine = 'MyISAM'
ORDER BY table_name;

Output

OUTPUT

+--------------+--------+
| TABLE_NAME   | ENGINE |
+--------------+--------+
| legacy_posts | MyISAM |
| legacy_users | MyISAM |
+--------------+--------+
2 rows in set

2. Make a backup first

Always take a backup before changing the engine, and test the conversion on a copy of the database, with timing. A conversion rewrites the whole table.

3. Convert

query.sql

SQL

1
2

ALTER TABLE legacy_users ENGINE = InnoDB;
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'learn' ORDER BY table_name;

Output

OUTPUT

+--------------+--------+
| TABLE_NAME   | ENGINE |
+--------------+--------+
| legacy_posts | MyISAM |
| legacy_users | InnoDB |
+--------------+--------+
2 rows in set

ALTER TABLE ... ENGINE = InnoDB rebuilds the table: it copies every row into a new InnoDB table, and swaps. It needs free disk space about the size of the table, and the table is locked for writes during the copy in the old MyISAM sense (MyISAM cannot do online DDL). A table with 50 million rows can take a long time. Plan a window, or use an online schema change tool (gh-ost, pt-online-schema-change) on a live system.

Convert several tables with generated statements:

query.sql

SQL

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE = InnoDB;') AS statement
FROM information_schema.tables
WHERE table_schema = 'learn' AND engine = 'MyISAM'
ORDER BY table_name;

Output

OUTPUT

+-----------------------------------------------------+
| statement                                           |
+-----------------------------------------------------+
| ALTER TABLE `learn`.`legacy_posts` ENGINE = InnoDB; |
+-----------------------------------------------------+
1 row in set

Review the generated list, and run it yourself.

4. Things that change after the conversion

Full-text indexes. InnoDB supports FULLTEXT, but the behaviour differs from MyISAM’s: the 50 percent rule is not applied in boolean mode, the minimum word length is innodb_ft_min_token_size (3, not 4), and changes become searchable after commit. Test your searches.

query.sql

SQL

1
2

ALTER TABLE legacy_posts ENGINE = InnoDB;
SELECT id, title FROM legacy_posts WHERE MATCH(title) AGAINST ('post') ORDER BY id;

Output

OUTPUT

+----+-------------+
| id | title       |
+----+-------------+
|  1 | First post  |
|  2 | Second post |
+----+-------------+
2 rows in set

Row size limits. InnoDB has a maximum row size (about 8 KB on-page, with off-page storage for long values). A MyISAM table with many wide VARCHAR columns may fail with “Row size too large”. Convert some columns to TEXT, or reduce lengths.

**COUNT(*) gets slower.** MyISAM stored the exact row count. InnoDB must count. If a page shows “total rows” on every request, cache the count (COUNT).

Auto-increment behaviour. InnoDB’s counter is persisted in MySQL 8.0 and later (in older versions it was recalculated at restart).

No table-level locks as a side effect. Code that relied on MyISAM’s table locks (LOCK TABLES) to avoid races needs real transactions and row locks (Locking).

Disk size. The InnoDB table is usually larger.

Primary key. MyISAM allowed tables without a primary key. InnoDB creates a hidden row id, which is poor for performance and for replication. Add a primary key to every table:

query.sql

SQL

CREATE TABLE no_key (name VARCHAR(20), city VARCHAR(20)) ENGINE = MyISAM;
INSERT INTO no_key VALUES ('Asha', 'Pune'), ('Ben', 'Delhi');
ALTER TABLE no_key ENGINE = InnoDB, ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
SELECT id, name, city FROM no_key ORDER BY id;

Output

OUTPUT

+----+------+-------+
| id | name | city  |
+----+------+-------+
|  1 | Asha | Pune  |
|  2 | Ben  | Delhi |
+----+------+-------+
2 rows in set

5. Add what MyISAM could not do

After converting, you can add the integrity rules that were missing:

query.sql

SQL

1
2
3

ALTER TABLE legacy_users ENGINE = InnoDB;
ALTER TABLE legacy_posts ADD CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES legacy_users (id);
SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema = 'learn' AND table_name = 'legacy_posts' ORDER BY constraint_name;

Output

OUTPUT

+-----------------+-----------------+
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-----------------+-----------------+
| fk_posts_user   | FOREIGN KEY     |
| PRIMARY         | PRIMARY KEY     |
+-----------------+-----------------+
2 rows in set

If the data already contains orphans (posts for users that do not exist), the foreign key fails. Find and fix them first (Finding Missing Rows).

6. Fix the source of the MyISAM tables

Edit the CREATE TABLE scripts and dumps: remove ENGINE=MyISAM.
Do the same in application installers and migrations.
When restoring an old dump, you can convert on the fly with a text replacement (sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g'), and then check the result.

7. Verify

query.sql

SQL

1
2

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'learn' AND table_type = 'BASE TABLE' ORDER BY table_name;
CHECK TABLE legacy_users, legacy_posts;

Output

OUTPUT

+--------------+--------+
| TABLE_NAME   | ENGINE |
+--------------+--------+
| legacy_posts | InnoDB |
| legacy_users | InnoDB |
| no_key       | InnoDB |
+--------------+--------+
3 rows in set

+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| learn.legacy_users | check | status   | OK       |
| learn.legacy_posts | check | status   | OK       |
+--------------------+-------+----------+----------+
2 rows in set

CHECK TABLE confirms the converted tables are healthy. Then run your application’s tests against the new tables, with attention to the points above.

A conversion checklist

[ ] Backup taken, and restore tested.
[ ] Converted a copy first, and measured the time and the new size.
[ ] Enough free disk space (about the size of the largest table, plus margin).
[ ] Maintenance window, or an online tool for big live tables.
[ ] Every table has a primary key.
[ ] Full-text searches tested.
[ ] Code that used LOCK TABLES reviewed.
[ ] Foreign keys added where relationships exist.
[ ] Source scripts no longer mention MyISAM.

lightbulb

Convert one table at a time

Start with small tables to learn how long it takes and what breaks. Leave the biggest tables for a planned window.