Please note that, for the scope of this article, Oracle MySQL and MariaDB are one and the same.
Convert MyISAM tables to InnoDB in MySQL
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 engine=MyISAM online...
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.
Step 1, list a complete ALTER TABLE statement for MyISAM tables
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;
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.
After the ALTER TABLE statement you 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 ',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
Don't forget to check, repair and optimize MySQL tables afterwards.
More reading information on converting MyISAM to InnoDB: Converting Tables from MyISAM to InnoDB on dev.mysql.com.
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.