Do you need to convert your tables from InnoDB to MyISAM?

If your database grows in size for no apparent reason, or if it takes longer than usual to process any query, it is more than likely that you need to change your tables from InnoDB to MyISAM.

Although the first MySQL storage engine is more reliable, consistent and robust than the second, we have come across quite a few websites where it causes serious performance problems and excessive growth of the tables, especially in the wp_options, wp_posts and wp_postmeta tables.

We have detected these problems on websites hosted under Parallels Plesk Panel, cPanel and Vesta Control Panel, so it is independent of the type of control panel your server uses and even of the versions of MySQL, PHP or WordPress you have installed.

How do we do it?

In principle we can do it in two ways.

First of all we can create a simple PHP file in the root directory of WordPress, which we will call cambia-tablas.php, for example, and in which we will include this simple, easy but effective PHP code:

We can also do it by running this SQL query directly in our phpMyAdmin, MySQL Workbench or MySQL Utilities, for example:

Where we only have to change base_de_datos for the name of our own database and the query will return a table with several ALTER statements, specifically a list similar to this one:

Once we have the list of statements, we only have to copy them all and run them inside the database whose tables we want to convert from InnoDB to MyISAM.

Note that as well as converting the tables to MyISAM we force the collation to UTF-8, specifically to utf8_general_ci, which will mean we get rid of strange characters in our database.

Benefits

The benefits we have obtained by making these modifications have been two.

On the one hand we have managed to slim down databases that were several GB in size to a few hundred MB. The reason is that InnoDB tables do not allow optimization, and that sometimes makes them fill up with unnecessary records.

And on the other we have managed to reduce the processing times of some queries considerably, by up to 74%. Normally caused by that same excessive size of the tables. By removing that large amount of unnecessary records, we considerably reduce the processing times of those queries.

Conclusion

If you are suffering from any of the problems described, try using this small trick on your database. If the problem persists, then you should leave your website in the hands of a WordPress specialist such as APG.