Latest Register Log In

+ Advanced Search

Converting MyISAM to InnoDB

Converting MyISAM to InnoDB
By
10/05/13 (Edited 05/06/14)

If your site's database has grown large -- hundreds of thousands of topics, forums or posts -- 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 topics or similiar forums, 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 forums" and "similiar topics" switches are turned off (you can't use these without fulltext).

Using phpmyadmin, go to the yourprefix_forums 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.




Description InnoDB provides superior performance for large databases, but only recent versions support fulltext.
Rating
Views 345 views. Averaging 0 views per day.