The solution for me is to use InnoDB tables for everything, but duplicate the searchable data into a MyISAM table. I then use MySQL triggers to keep the MyISAM table's data automatically synced up with the InnoDB. If the MyISAM table gets corrupted, I can wipe it and copy the data back over with extreme ease.
To demonstrate, assume that I have a table called `links` with all sorts of columns and rows in it. Of these columns, I want the title and description to be searchable.
The first step is to create a separate MyISAM table to store the searchable data. Make sure the column definitions are the same, but only include the primary key and the rows you want to search.
CREATE TABLE `links_searchable` (
`id` int(10) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
`description` varchar(1000) NOT NULL
The next step is to create a fulltext index on the searchable columns, so that MySQL will allow the MATCH() queries to be executed on these columns.
create fulltext index `ft` on links_searchable (title,description);
If your `links` table has any data in it, go ahead and copy it over now
insert links_searchable (id,description,title) select id,description,title from links;
Now create the MySQL triggers so that you don't have to write extra code to keep these tables synced. Once these triggers are created, you can forget about this extra table until it's actually time to search.
CREATE TRIGGER ins_searchable AFTER INSERT ON `links`
FOR EACH ROW
insert into links_searchable (id,description,title) values(NEW.id,NEW.description,NEW.title);
CREATE TRIGGER del_searchable AFTER DELETE ON `links`
FOR EACH ROW
DELETE FROM links_searchable WHERE id=old.id;
CREATE TRIGGER upd_searchable AFTER UPDATE ON `links`
FOR EACH ROW
UPDATE links_searchable SET title=NEW.title, description=NEW.description WHERE id=NEW.id;
At this point, you should be able to run full text searches on your data. Here is a MATCH() query that returns all of the relevant links data. The results are automatically sorted by relevance.
select links.* from links, links_searchable where MATCH (links_searchable.title,links_searchable.description) AGAINST ('query') and links.id=links_searchable.id
Now, if you are like me, you hate stopwords. Disabling them is pretty easy in Ubuntu. Simply add or update these lines in your /etc/mysql/my.cnf under [mysqld]
ft_stopword_file = ""
ft_min_word_len = 1
Then restart MySQL
sudo service mysql restart
And finally, reindex your searchable table
repair table links_searchable;
It's that easy. Enjoy all of the benefits of both MyISAM and InnoDB by using both at the same time :)