Starting with version 3.2, ExpressionEngine’s database tables use the InnoDB storage engine instead of MyISAM. InnoDB has stronger support for write heavy loads, which makes it optimal for sites with a lot of user submitted content. It has also seen fairly active development in the past few years and has become the default of choice for many applications.
If you have an existing site and wish to convert it to use InnoDB, please read this document carefully before beginning.
Note: This guide is for ExpressionEngine 3.2 and higher. Please update before following these directions.
Before beginning, please make sure you have a full backup of your database. Refer to the backup documentation for a step-by-step guide to creating good backups.
After converting your database, your backup strategy may need to be reviewed. Some backup scripts will only handle specific database engines.
Different storage engines have different configuration requirements. Please talk to your hosting provider or dev-ops team to make sure your environment is set up to support this transition. This is especially important if you run a busy site that is already tuned for your current environment. As a rule of thumb, if your site is working well a transition is not required.
Some third party tables may be using fulltext indexes, which are only available to very recent versions of InnoDB. An easy way to find these indexes is to use this query,
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE TABLE_SCHEMA = 'database_name' AND index_type LIKE 'FULLTEXT%'
If you find any fulltext tables you will not be able to proceed with the conversion. Contact the developer of the add-on for alternative options.
Finally you can run the conversion. For each table in the database run this query:
ALTER TABLE table_name ENGINE=InnoDB;
We recommend doing this one table at a time. This process may take several minutes to complete.