To convert a non-
InnoDB table to use
ALTER TABLE t1 ENGINE=InnoDB;
Do not convert MySQL system tables in the
mysql database (such as
host) to the
InnoDB type. This is an unsupported
operation. The system tables must always be of the
InnoDB does not have a special optimization
for separate index creation the way the
MyISAM storage engine does. Therefore, it
does not pay to export and import the table and create indexes
afterward. The fastest way to alter a table to
InnoDB is to do the inserts directly to an
InnoDB table. That is, use
TABLE ... ENGINE=INNODB, or create an empty
InnoDB table with identical definitions and
insert the rows with
INSERT INTO ... SELECT * FROM
If you have
UNIQUE constraints on secondary
keys, you can speed up a table import by turning off the
uniqueness checks temporarily during the import operation:
... import operation ...SET unique_checks=1;
For big tables, this saves a lot of disk I/O because
InnoDB can then use its insert buffer to
write secondary index records as a batch. Be certain that the
data contains no duplicate keys.
unique_checks permits but does
not require storage engines to ignore duplicate keys.
To get better control over the insertion process, it might be good to insert big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something AND yourkey <= somethingelse;
After all records have been inserted, you can rename the tables.
During the conversion of big tables, you should increase the
size of the
InnoDB buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the
Make sure that you do not fill up the tablespace:
InnoDB tables require a lot more disk space
MyISAM tables. If an
ALTER TABLE operation runs out of
space, it starts a rollback, and that can take hours if it is
disk-bound. For inserts,
InnoDB uses the
insert buffer to merge secondary index records to indexes in
batches. That saves a lot of disk I/O. For rollback, no such
mechanism is used, and the rollback can take 30 times longer
than the insertion.
In the case of a runaway rollback, if you do not have valuable data in your database, it may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see Section 18.104.22.168, “Forcing InnoDB Recovery”.
If you want all your (nonsystem) tables to be created as
InnoDB tables, add the line
default-storage-engine=innodb to the
[mysqld] section of your server option file.