< Back to Optimization

Converting MyISAM to InnoDB

If your site's database has grown large -- hundreds of thousands of articles, categories or comments -- you may want to switch the database engine from MyISAM to InnoDB to improve performance. InnoDB is designed to handle large databases well.

There's just one big drawback: MySQL versions prior to 5.6.4 don't support fulltext indexes in InnoDB. This means you lose the ability to display similiar articles or similiar categories, or to perform fulltext searches. If you have a dedicated server you can update to MySQL 5.6, but if you're on a shared host it may be years before you get it.

If you'd like to use InnoDB with MySQL < 5.6.4, you'll need to remove fulltext indexes from the tables before conversion or the tables won't convert. First, on your WSN site go to Admin -> Settings -> Searches and make sure that your default search type is not full mysql fulltext -- if it is, choose another type. Then, go to Admin -> Settings -> Switches and make sure the "similiar categories" and "similiar articles" switches are turned off (you can't use these without fulltext).

Using phpmyadmin, go to the yourprefix_categories and yourprefix_links tables of your database. Click the "structure" tab, and near the bottom of the page it'll list the indexes on the table. Click the red x to remove any index which is labeled as a FULLTEXT index.

Now you're ready to convert to InnoDB. To do so, go to Admin Panel -> Miscellaneous -> Advanced on your WSN site. In the run custom PHP box on the right side, run this PHP code:
global $tables, $db; reset($tables); foreach ($tables as $t) $db->query("ALTER TABLE $t ENGINE = INNODB");


This should convert all your WSN tables, as long as their fulltext indexes have been removed first. You should now be experiencing better database performance, especially on insertions.