MySQL Cookbook, 2nd Edition

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