Full Text Searching InnoDB Tables on MySQL 5.5 with Trigger-based Table Syncing

As you may be aware, MySQL 5.6 supports full text searches on InnoDB tables, but few are willing to upgrade yet because 5.6 is currently unstable and not yet suitable for production servers. This means that the only way to use full text searching in MySQL is to put this data into a MyISAM table. Unfortunately MyISAM tables suffer from a number of faults, the most worrisome being data integrity.

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
) ENGINE=MyISAM;

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 :)