How to change the storage engine a MySQL table uses

The last MySQL post looked at how to tell which storage engine a MySQL table uses by running a SQL query or using phpMyAdmin. This post looks at how to change the storage engine used by a MySQL table using either a SQL query or phpMyAdmin.

SQL Query

Using the example tables from the previous post, if we wanted to change the "products" table from MyISAM to INNODB run this SQL query:

ALTER TABLE products ENGINE = innodb

NOTE: It is not recommended to do this on a large table on a busy production website without taking the site down first (and doing it a low traffic periods) as it can take some time to rebuild the table. Obviously the table cannot be accessed while the engine type is being changed.

phpMyAdmin

You can easily change the engine type for a MySQL table using phpMyAdmin, which saves you having to remember the SQL query to run. The screenshots and instructions shown below are for the phpMyAdmin 2.x branch. It may be different in the 3.x branch which I have not yet used.

Log into phpMyAdmin, select the database and then click the appropriate table in the left navigation area. In the main pane you will see the list columns etc. At the top is a navigation area, as shown in the screenshot below, with "Browse", "Structure" etc. Click the "Operations" button.

The next page has a "table options" section as shown in the screenshot below. Select the storage engine you would like to change the table to use and then click the "Go" button.

NOTE: As noted above, it is not recommended to do this on a large table on a busy production website without taking the site down first etc etc. And, in the case of a large table, you are probably better to run the SQL query from the MySQL command line then using phpMyAdmin in case of timeouts or other issues.

Related posts:

• PHP script to convert MySQL tables to a new storage engine
• Use one file per table with MySQL's INNODB storage engine
• Find which tables use INNODB with MySQL
• How to tell which storage engine a MySQL table uses
• Renaming a table in MySQL
• Check if a MySQL table exists