Convert MyISAM tables to InnoDB. In the earlier days of MySQL, the default storage engine for your database was MyISAM. This is also why you still encounter a lot of examples with
engine=MyISAM online. Nowadays, the default storage engine is InnoDB, and MyISAM is no longer actively developed. Therefore, all MySQL performance optimizations are for InnoDB, and it’s recommended to switch to this as your table storage engine.
Convert MyISAM to InnoDB
MySQL storage engine, MyISAM versus InnoDB: if you want to change a MyISAM table to InnoDB, the process is fairly easy and straightforward.
You can use the following guidelines and tips, if you want to change existing MyISAM tables to InnoDB. Doing so provides better reliability and scalability.
Let’s assume your tables are originally MyISAM, which was the default. Here’s how to convert your tables, the fast and easy way. Use your MySQL command line prompt or phpMyAdmin for these steps.
Step 1, list a complete ALTER TABLE statement for MyISAM tables
First of all: the following query will list an
ALTER TABLE statement for all tables in your database. You need this in step 2.
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema');
Next, you’ll need the generated list, and it looks something like:
ALTER TABLE db-name.table1 ENGINE=InnoDB; ALTER TABLE db-name.table2 ENGINE=InnoDB; ALTER TABLE db-name.table3 ENGINE=InnoDB;
Step 2, execute ALTER TABLE statements on your database
Finally: execute these
ALTER TABLE on your database. Copy and paste these statements into your MySQL command interface or phpMyAdmin.
ALTER TABLE statement we need to optimize all database tables.
Optimize all InnoDB tables with one prepared statement
Use the following statement to optimize all MySQL tables in your database:
SELECT CONCAT('OPTIMIZE TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @optimizecmd FROM information_schema.tables WHERE table_schema=database(); PREPARE `s2` FROM @optimizecmd; EXECUTE `s2`; DEALLOCATE PREPARE `s2`;
More reading information on converting MyISAM to InnoDB:
Converting Tables from MyISAM to InnoDB.
For WordPress, I co-created the Vevida Optimizer plugin. This plugin can convert your database tables automatically. Furthermore you can configure and fine-tune WordPress automatic updates with the plugin.
Speed things up before changing MyISAM to InnoDB
Before you convert the table engine, you can adjust the order so that the primary key column is in order:
ALTER TABLE tablename ORDER BY 'primary_key_column';
This will pre-arrange the table so that it can be converted quickly. This saves a lot of re-arranging required in MySQL.
In conclusion: Convert MyISAM to InnoDB for better database performance.