WP-Mix

Originally, the WordPress database was formatted as type MyISAM. Then years later they changed the database format to InnoDB. Reportedly, InnoDB is superior to MyISAM in several ways. This is true for any dynamic site, not just WordPress. If your database is not InnoDB, you may benefit from changing it. This quick tutorial explains how to change any MySQL database table to InnoDB or MyISAM (or any valid type) using a simple SQL command.

Important! Before making any changes to your database, make sure you have a good working backup copy. Just in case something goes awry, you can restore your previous database asap.

Change Database Type to InnoDB

Unfortunately there is not a single SQL command to change the type of ALL tables in a MySQL database. Instead you have to change each table one at a time. Here is the command to run for each table that you want to convert to InnoDB:

ALTER TABLE wp_downloads ENGINE=InnoDB;

Before entering that command, replace the table name wp_downloads to match the name of the table that you want to change. Then enter the command and repeat the process for each table in the database that you want to change to InnoDB format.

Change Database Type to MyISAM

If you are getting error messages such as:

Table does not support optimize…

The storage engine for the table doesn’t support repair.

It happens because the table doesn’t support certain functions like Optimize or Repair. For example, WordPress users rocking InnoDB format will see those error messages when they try to optimize or repair their database tables. So if you want to change your table format to MyISAM, here is the magic SQL command:

ALTER TABLE wp_downloads ENGINE=MyISAM;

Before entering that command, replace the table name wp_downloads to match the name of the table that you want to change. Then enter the command and repeat the process for each table in the database that you want to change to MyISAM format.

Change Database Type to Anything

If you examine the SQL commands that we are using in this tutorial, you will notice they are basically the same. The only difference is the type that you want to use. So logically you can use the same command to change the table format to any valid type:

ALTER TABLE wp_downloads ENGINE=ANYTHING;

Simply replace ANYTHING with the type that you want to use. Also remember to replace the table name wp_downloads to match the name of the table that you want to change. Then enter the command and repeat the process for each table in the database that you want to change to whatever format.

Bonus: Define the Type for New Database Tables

What if you want to define the type for a new database table? Easy. You can specify the type using the ENGINE parameter. Here is an example:

CREATE TABLE `wp_downloads` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`host`  varchar(200) NOT NULL default '',
	`agent` varchar(200) NOT NULL default '',
	`refer` varchar(200) NOT NULL default '',
	`ip`    varchar(200) NOT NULL default '',
	`cur_time` varchar(200) NOT NULL default '',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

This code creates a new table called wp_downloads that will have the type InnoDB. You can see where InnoDB is specified in the last line. So if you want to set the type as something else, simply replace InnoDB with MyISAM or whatever format you want to use.