To convert a non-InnoDB
table to use
InnoDB
use ALTER
TABLE
:
ALTER TABLE t1 TYPE=InnoDB;
Do not convert MySQL system tables in the
mysql
database (such as
user
or host
) to the
InnoDB
type. This is an unsupported
operation. The system tables must always be of the
MyISAM
type.
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 ALTER
TABLE ... TYPE=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, starting from MySQL 3.23.52, you can speed up a table
import by turning off the uniqueness checks temporarily during
the import operation:
SET unique_checks=0;
... 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
allows 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 InnoDB
log files.
Make sure that you do not fill up the tablespace:
InnoDB
tables require a lot more disk space
than 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 13.2.7.2, “Forcing InnoDB
Recovery”.
If you want all your (nonsystem) tables to be created as
InnoDB
tables, you can, starting from the
MySQL 3.23.43, add the line
default-table-type=innodb
to the
[mysqld]
section of your server option file.
User Comments
Add your own comment.