Switching Over To InnoDB From MyIsam

February 09, 2013

InnoDB can be a complete pain to manage, troubleshoot and fix…but this is all wiped out by its impressive performance improvements as documented all over the place. Now simply switching to InnoDB on your tables should increase your performance but you can go even further by optimizing them to take advantage of some of the core features of InnoDB. Either way, switching should be your first step, lets get started!

Switching 1 Table

If your running a forum or other software that traffics a majority of its data out of 1 table, it may make sense just to change that 1 table rather than change the entire databse. As such, the following command should suite you just fine. Log into your mysql console and run the following:

ALTER TABLE table_name ENGINE=InnoDB;

Simple as that! You should of course always make backups before running this just in case. To verify the InnoDB tables in the system, log back into your mysql and run this:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Switching 1 Database

Of course switching just 1 table wouldnt be enough, lets bring it up to 1 complete database. Having a table under InnoDB should yield some solid improvements, having an entire DB is even better. We have two options, a bash script and a mysqldump.

Bash Script

TABLE=db_name

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema IN ('${TABLE}') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

This will then output the appropriate mysql commands to alter all tables defined in the TABLE variable. The reason why this outputs it to a sql file rather than just runs the command is so that you have the ability to manually review it and ensure nothin wonky happened. To apply this, run the following:

mysql < /root/ConvertMyISAMToInnoDB.sql

MySQL Dump

Im just providing this so you have multiple options. Here is a way that you can do the same affect but you change everything over by dumping. This is useful if you want to duplicate the dump first for testing:

mysqldump [database]  | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/' > [innodb-database.sql]

Changing Everything

You could change all of your tables over by doing a dump or multiple dumps and sedding out the content similar to the above, I just dont see a use in it. Here’s a shell script that performs similar functions to the above only it selects all myisam tables except those in information_schema, mysql or performance_schema:

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

Then verify and apply it with the following:

mysql < /root/ConvertMyISAMToInnoDB.sql