In the earlier days of MySQL, the default storage engine for your database tables was “MyISAM”. InnoDB is the engine to use now, and MyISAM is no longer actively developed. Therefore all MySQL optimizations are for InnoDB, and it’s recommended to switch from MyISAM to this InnoDB storage engine for your MySQL database tables. Follow this tutorial on how to convert from MyISAM to InnoDB.
Please note that, for the scope of this article, Oracle MySQL and MariaDB are one and the same.
MySQL storage engine, MyISAM versus InnoDB: if you want to change a MyISAM table to InnoDB, the process is fairly easy and straightforward.
You want to move from MyISAM to InnoDB for at least five reasons:
You can use the following guidelines and tips in this post, if you want to convert your existing MyISAM database tables to InnoDB. Doing so provides better reliability and scalability.
Let’s assume your tables are originally MyISAM, which was the default and is why you still see so many examples with
Here’s how to convert your tables, the fast, safe and easy way. You can use these commands on your MySQL command line prompt or in phpMyAdmin. As a bonus, this post explains how to optimize your tables after a successful conversion.
You need the ALTER TABLE operation to change a storage engine. So first you have to create a full list of your tables with an ALTER TABLE operation. The following query will do so, and list an
ALTER TABLE statement for all tables in your database. You need this in step 2.
For security we exclude MySQL main tables ‘information_schema’, ‘mysql’ and ‘performance_schema’.
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;
Finally: execute these
ALTER TABLE on your database. Copy and paste these statements into your MySQL command interface or phpMyAdmin.
ALTER TABLE statement you need to optimize all database tables.
Use the following statement to optimize all MySQL tables in your database:
SELECT CONCAT('OPTIMIZE TABLE ',SELECT CONCAT('ALTER TABLE ',GROUP_CONCAT('`',table_schema,'`','.','`',table_name,'`'),';') FROM information_schema.tables WHERE table_schema=database();,';') INTO @optimizecmd FROM information_schema.tables WHERE table_schema=database(); PREPARE `s2` FROM @optimizecmd; EXECUTE `s2`; DEALLOCATE PREPARE `s2`;
Yes, this looks very complicated. What the above command does is a number of queries and sub-queries:
More reading information on converting MyISAM to InnoDB: Converting Tables from MyISAM to InnoDB.
For WordPress, I co-created the Vevida Optimizer plugin. You can use this plugin to convert your database tables automatically. Furthermore you can configure and fine-tune WordPress automatic updates with the plugin.
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.
My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.
System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” with MySQL Connector/NET and utf8mb4 character set
Restore single MySQL table from a full mysqldump backup file
MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!
How to string replace on all WordPress posts in MySQL