Create an empty InnoDB  table with identical column and index definitions. Use SHOW CREATE TABLE table_name \G  to see the full CREATE TABLE  statement to use. Change the ENGINE  clause to ENGINE=INNODB . 

## Transferring Data 

To transfer a large volume of data into an empty InnoDB  table created as shown in the previous section, insert the rows with INSERT INTO innodb_table  SELECT * FROM myisam_table  ORDER BY primary_key_columns . 

You can also create the indexes for the InnoDB  table after inserting the data. Historically, creating new secondary indexes was a slow operation for InnoDB , but now you can create the indexes after the data is loaded with relatively little overhead from the index creation step. 

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: 

SET unique_checks=0; ... import operation ... SET unique_checks=1; 

For big tables, this saves disk I/O because InnoDB  can use its change 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. 

For better control over the insertion process, you can insert big tables in pieces: 

INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something  AND yourkey <= somethingelse ; 

After all records are inserted, you can rename the tables. 

During the conversion of big tables, increase the size of the InnoDB  buffer pool to reduce disk I/O. Typically, the recommended buffer pool size is 50 to 75 percent of system memory. You can also increase the size of InnoDB  log files. 

## Storage Requirements 

If you intend to make several temporary copies of your data in InnoDB  tables during the conversion process, it is recommended that you create the tables in file-per-table tablespaces so that you can reclaim the disk space when you drop the tables. When the innodb_file_per_table  configuration option is enabled (the default), newly created InnoDB  tables are implicitly created in file-per-table tablespaces. 

Whether you convert the MyISAM  table directly or create a cloned InnoDB  table, make sure that you have sufficient disk space to hold both the old and new tables during the process. InnoDB  tables require 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 15.21.2, “Forcing InnoDB Recovery” . 

## Defining Primary Keys 

The PRIMARY KEY  clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value. 

