Home arrow MySQL arrow Page 10 - Storage Engine (Table Types)

Table Conversions - MySQL

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.)

  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article



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:


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.


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:

INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;

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.

Buy the book!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.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: