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:
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:
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.
1 2 3 4
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:
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:
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:
1 2 3
Then verify and apply it with the following:
And that does it! Enjoy the wonderful performance improvements that is InnoDB.