It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM table few times, though Table Locks is probably the most important issue – with modern multi core servers not only the fact you can’t well mix SELECTs and UPDATEs but also the fact only one update can be happening at the time can be the problem, not to mention Key Cache which often becomes serious contention issue.
The problem we often run into during migration is Full Text Search indexes which are not supported for Innodb tables. So what can you do ?
Leave Tables as MyISAM The beauty of MySQL storage engines is you do not have to convert all tables at once. In some cases full text search is used on secondary small tables which do not cause problems with contention or anything else. So this can be valid choice. Unfortunately in many cases the tables you want to do full text search on are intensively used and this may not be the option.
Use MyISAM Slaves In some cases it may be justified to keep table as MyISAM on one or several of slaves and use it for full text search queries. This approach is helpful if migration has to be performed very quickly and it takes a lot of time to implement any significant changes to schema or queries. In general cross storage engine replication is not my favorite approach but sometimes it is less of the evils.
Use “Shadow” MyISAM Table You can keep main data in Innodb but build a “shadow” MyISAM table which is used for full text search. In certain cases you can just use MySQL triggers to maintain such table, in other cases this would not work as this would add a lot of contention on the writes. In this case you can just rebuild the table from Innodb source on regular basics. In case you can afford to have stale data in search results it can work pretty well.
Use Sphinx or other external full text search engine This is what we use a lot especially if performance of full text search is an issue to. This eliminates the need to have MyISAM table anywhere. We use Sphinx in a lot of cases as it is very easy to get going. For simple applications it often takes just couple of hours to get the full text search running Sphinx instead of build in full text search.
Resources
RELATED POSTS
