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:
- MyISAM is old and no longer being developed
- InnoDB is much faster
- InnoDB supports transactions, which means you can commit and roll back. MyISAM does not.
- InnoDB is more reliable as it uses transactional logs for auto recovery. MyISAM does not.
- InnoDB is much, much faster! Especially if you have optimized MySQL for a heavy InnoDB workload.
- MyISAM cannot be used in a high-available, high performance MariaDB Galera cluster or Percona XtraDB Cluster. InnoDB can.
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:
- select all database table names, concatenated with the current table schema (database name), from information_schema.tables where it matches the current database name. Also, backtick ‘table_schema’ and ‘table_name’.
- concatenate the above with an ‘OPTIMIZE TABLE’ statement
- put it all into a user-defined variable ‘@optimizecmd’
- prepare an statement ‘s2’ from ‘@optimizecmd’ and execute it next.
- At last, deallocate the prepared statement
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 or guru. I am merely a systems administrator, doing my daily thing at Vevida.
If you feel a post has helped solve your problem, or has saved you time, please consider making a donation. You can transfer a direct donation through Paypal or via bank wire-transfer IBAN: NL31 ABNA 0432217258 (Jan Reilink). Thanks!