Recipe 4.4. Checking or Changing a Table s Storage Engine

Recipe 4.4. Checking or Changing a Table's Storage Engine

Problem

You need to check which storage engine a table uses so that you can determine what engine capabilities are applicable. Or you need to change a table's storage engine because you realize that another engine has capabilities that are more desirable for the way you use the table.

Solution

To determine a table's storage engine, you can use any of several statements. To change the table's engine, use ALTER TABLE with an ENGINE clause.

Discussion

MySQL supports several storage engines, each of which have differing characteristics. For example, the InnoDB and BDB engines support transactions, whereas MyISAM does not. If you need to know whether a table supports transactions, check which storage engine it uses. If you need to use the table in transactions but the table's engine does not support them, you can convert the table to use a transaction-capable engine.

To determine the current engine for a table, check INFORMATION_SCHEMA or use the SHOW TABLE STATUS or SHOW CREATE TABLE statement. For the mail table, obtain engine information as follows:

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'mail'; +--------+ | ENGINE | +--------+ | MyISAM | +--------+ mysql> SHOW TABLE STATUS LIKE 'mail'\G *************************** 1. row ***************************            Name: mail          Engine: MyISAM ... mysql> SHOW CREATE TABLE mail\G *************************** 1. row ***************************        Table: mail Create Table: CREATE TABLE `mail` (   `t` datetime DEFAULT NULL,   `srcuser` char(8) DEFAULT NULL,   `srchost` char(20) DEFAULT NULL,   `dstuser` char(8) DEFAULT NULL,   `dsthost` char(20) DEFAULT NULL,   `size` bigint(20) DEFAULT NULL,   KEY `t` (`t`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

To change the storage engine used for a table, use ALTER TABLE with an ENGINE specifier. For example, to convert the mail table to use the InnoDB storage engine, use this statement:

ALTER TABLE mail ENGINE = InnoDB;

Be aware that converting a large table to a different storage engine might take a long time and be expensive in terms of CPU and I/O activity.

Recipe 4.4. Checking or Changing a Table s Storage Engine