How to change all of your DB tables from MyISAM to InnoDB

[LiquidFractal]

First off: I am not a SQL expert of any kind, so I will not field questions about this procedure - I'm just sharing what I just did on my website to switch over DB tables to InnoDB as per Invision Support-page recommendations.  The code isn't mine - I lifted it off the web somewhere.

If you've been wondering how to switch over your MyISAM tables to (what I understand as) the more stable and optimized InnoDB storage engine, look no further!  It might be best for everyone to do this anyway, as I read somewhere that MyISAM is now deprecated and, I suppose, on the way out eventually(?).

Anyway - to do what follows you will need access to phpMyAdmin or some similar interface which allows you to perform direct SQL queries.  Make backups - I do not assume any responsibility for anything that breaks as a result of this!

First, go in to phpMyAdmin (or whatever) and make sure your Invision DB is selected.  This procedure assumes that you have one DB which is dedicated to IPB with no other app tables in the DB (it may still work, but I have no way of trying it out and I don't know enough about SQL to be sure it will work in a shared DB).

In the SQL query box, run the following code.  Be sure to replace YOUR_DB_NAME_HERE with the name of your Invision DB:

SET @DATABASE_NAME = 'YOUR_DB_NAME_HERE';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC

The output will include a list of SQL commands linked to your existing MyISAM tables.  Each line, when run, will convert the MyISAM table in question to InnoDB as per the following example:

ALTER TABLE my_table ENGINE = InnoDB;

You can run each line separately, or (recommended) run batches of them at once.  Just make sure the syntax is correct for each line (phpMyAdmin will truncate longer lines, but will show you if a line has improper syntax).

You may need to do several passes in order to switch over all your tables.  My procedure was as follows: I ran a series of ALTER TABLE statements at a time, then re-ran the SQL query in the above box to get a revised list of remaining MyISAM tables.  Then I'd run the next batch of generated ALTER TABLE lines, and so on until running the SQL query block above generated no results (which means that they've all been converted and you have no more MyISAM tables).

That's it!  Browse your DB and you should see all of them running under innoDB.

If there's a more professional and streamlined way to do this (especially for very big websites), I encourage those with the knowledge to post - again, I'm just posting what worked with my VPS.

Hope this helps someone out there!  I swear I can see a definite speed increase on my site as a result of switching!

Edited June 15, 2022 by LiquidFractal

[Marc]

There is probably a more efficient way, but I use the method above myself, purely as I dont have to look it up LOL. 

[LiquidFractal]

The one other thing I wanted to mention: unless you really really like running this procedure every so often to keep your DB InnoDB, you want to make InnoDB the default storage engine for your DB so that all future apps and plugins will create InnoDB tables instead of myISAM.

To do this, there's some helpful information in this thread:

Or, if you have a managed VPS (or something along those lines), contact your provider and ask them to do this for you.