Latest Register Log In

+ Advanced Search

Converting MyISAM to InnoDB

Converting MyISAM to InnoDB
By
Oct 05, 2013 (Edited May 06, 2014)

If your site's database has grown large -- hundreds of thousands of listings, 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 listings 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 listings" 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.




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