Storage Engine (Table Types) - Table Conversions
(Page 10 of 15 )
Several techniques are available to convert one table type to another, each with advantages and disadvantages. In the following sections, we cover three of the most common.
ALTER TABLE The easiest way to move a table from one engine to another is by using an ALTER TABLE statement. The following command converts mytable to BDB:
ALTER TABLE mytable TYPE = BDB;
Note: As of MySQL Versions 4.0.18 and 4.1.2, you may use ENGINE instead of TYPE. In a later version of MySQL (probably in the 5.x series), support for TYPE will be removed entirely.
The previous syntax works for all storage engines, but there’s a catch: it can take a lot of time. MySQL will perform a row-by-row copy of your old table into your new table. During that time, you’ll probably be using all the server’s disk I/O capacity, and the original table will be locked while the conversion runs. So take care before trying this technique on a busy table. Instead, you can use one of the following methods, which involve making a copy of the table first.
Dump and reimport To gain more control over the process, you might choose to dump the table to a text file using the mysqldump utility. Once the table is dumped, simply edit the dump file to adjust the CREATE TABLE statement it contains. Be sure to change the table name as well as its type because you can’t have two tables with the same name in the same database even if they are of different types.
If you import into InnoDB or BDB, be sure to use the --no-autocommit option to disable AUTOCOMMIT mode. Otherwise each individual insert will be performed in its own transaction.
The downside of using mysqldump is that it isn’t terribly fast and uses far more disk space. Not only will the dump file contain all the data from the table, it will also contain all the SQL necessary to repopulate the table. Also, you won’t be able to delete the dump file until the new table has been created.
Furthermore, if the dump file happens to be quite large, editing it can be a challenge. You can’t simply load a 6-GB file into vi or emacs on most systems.* Instead, you’ll need to craft a Perl or sed script to do the job.
* Maybe you can, but it’ll be pretty painful.
CREATE and SELECT The third technique is a compromise between the speed of the first mechanism and the safety of the second. Rather than dumping the entire table or converting it all at once, you create the new table and use MySQL’s INSERT INTO ... SELECT syntax to populate it incrementally. If, for example, you have a MyISAM table called myisam_ table that you’d like to convert to an InnoDB table named innodb_table, you need to run queries like this:
BEGIN;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;
Assuming that id is the primary key, you run that query using larger values of x and y each time until all the data has been copied to the new table. After doing so, you are left with the original table, which you can drop after you’re done with it, and the new table, which is now fully populated.
Alternatively, if you use MySQL 4.1 or newer, you can create the new table and copy the table in two steps:
CREATE TABLE newtable LIKE mytable;
INSERT INTO newtable SELECT * FROM mytable;
Whichever method you use, if you’re dealing with a large volume of data, it’s often more efficient to copy the data before adding indexes to the new table.
 | If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!
Visit the O'Reilly Network http://www.oreillynet.com for more online content. |
Next: The Storage Engines >>
More MySQL Articles
More By O'Reilly Media
/ 46 
