How to change the storage engine a MySQL table uses
Posted January 17th, 2009 in MySql (Updated June 27th, 2011)
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.
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.
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.
- PHP script to convert MySQL tables to a new storage engine (Monday, June 27th 2011)
- Use one file per table with MySQL's INNODB storage engine (Wednesday, June 17th 2009)
- Find which tables use INNODB with MySQL (Wednesday, June 10th 2009)
- How to tell which storage engine a MySQL table uses (Tuesday, January 13th 2009)
- Renaming a table in MySQL (Wednesday, July 2nd 2008)
- Check if a MySQL table exists (Friday, June 13th 2008)