Convert MyISAM tables to InnoDB

Background

MyISAM as the default storage system for database tables has been replaced by InnoDB since MySQL 5.5. The reasons for this were the lack of transaction security and referential integrity in MyISAM. With a few exceptions, database operations in InnoDB are also faster than in its predecessor.
Since MySQL 5.6 and MariaDB 10.0, InnoDB also supports full-text search/full-text indexing, for which MyISAM was previously used.

We therefore recommend that you switch your tables to InnoDB if there is no explicit dependency on MyISAM features. The conversion will guarantee you the highest level of stability and performance.

Solution

There are two ways to implement the conversion to InnoDB:

Use of phpMyAdmin

One way is to use phpMyAdmin:

• After logging in, select the database whose tables you want to change.
• In the column "Type" you can see the used storage system of the tables. You can sort this column to show the MyISAM tables grouped

• Click on the table name and then on the "Operations" tab.
• Under "Table options" you will find the line "Table format" in which you select "InnoDB" using the drop-down menu.
• After clicking "OK" the conversion will be performed.

Conversion via SQL commands

Another option is to convert directly using SQL commands with the following syntax:

ALTER TABLE t ENGINE=InnoDB;

To find all tables that use MyISAM, this command can be used:

SELECT TABLE_SCHEMA, TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_SCHEMA NOT IN("information_schema", "mysql", "performance_schema") AND ENGINE="MyISAM" ORDER BY TABLE_SCHEMA, TABLE_NAME;

If you need assistance with the conversion to InnoDB, please contact our support team at support@maxcluster.de.