Aug 172014
 
Article MySQL

In a previous post we have seen how to tell the storage type of a MySQL table, by means of a SQL query or with phpMyAdmin.

This post explains how to change the storage type of a table. Also in this case, this can be done by means of a SQL sentence, or using the phpMyAdmin frontend.

Changing the storage type of a table with a SQL statement

The storage type of a table can be changed with a “ALTER TABLE” sentence. For instance, to change to InnoDB the storage type of table  “wp_posts” in a wordpress database:

NOTE: if the volume of data in the table is large, and the table receives also a large number of accesses, as might happen in a high traffic web site, it is advisable to put the site in maintenance mode first, and make the change during the lower traffic time of day.

Changing the storage type of a table in the phpMyAdmin frontend

It is also possible to change the storage type using the phpMyAdmin interface. From the list of tables, select the table to be modified, and click on the “Operations” tab:

phpmyadmin-table-operations

 

 

This opens a new page that includes a section “Table Options”, with a dropdown where the desired storage type for the table can be selected:
phpmyadmin-change-storage

NOTE: As already mentioned, it is not advisable to carry out this action on a production server without first placing the site in maintenance mode. Besides, it is better to make the change in this case from the command line, rather than through the phpMyAdmin frontend, because timeouts or other issues could happen while the change is being done.

Index of posts related to MySQL

 Posted by at 4:18 pm

 Leave a Reply

(required)

(required)