This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)
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.
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!