Top Secret Project

One of the issues with using Full-text search in MySQL is that it requires the MyISAM table Engine. In MySQL tables need the InnoDB Engine to use transactions.

This means that we can only ever have full-text search, or transactions, but not both. Given that we really want transactions all the time*, we should generally be running with the InnoDB engine. Not to mention scaling and other issues**.

Enter the Shadow MyISAM Table Pattern to enable (near) Transactional Full-Text Search in MySQL.

All of the following code assumes assumes MySQL 5+.

Suppose you have a table with some content that you want to search using Full-Text, but also want to manage in a transactional environment:

CREATE TABLE content (
id int(10) unsigned NOT NULL auto_increment,
content text,
PRIMARY KEY (id)
) ENGINE=InnoDB;


We create a shadow table using MyISAM and a full-text index that maps the content using a foreign key:

CREATE TABLE content_search (
id int(10) unsigned NOT NULL auto_increment,
content_id int(10) unsigned NOT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT KEY index_fulltext (content)
) ENGINE=MyISAM;


.Then we add some triggers to update the shadow table automatically AFTER INSERT and AFTER UPDATE:

CREATE TRIGGER insert_content_search AFTER INSERT ON content
FOR EACH ROW
INSERT INTO content_search (content_id, content) VALUES (NEW.id, NEW.content);
CREATE TRIGGER update_content_search AFTER UPDATE ON content
FOR EACH ROW
UPDATE content_search SET title = NEW.title, content = NEW.content WHERE content_id = NEW.id;

Changes in the content table are now automatically reflected in the content_search table and this table gives you access to MySQL’s full-text search capability:
SELECT * FROM content_search s LEFT JOIN content c ON c.id = s.content_id WHERE MATCH (content) AGAINST ('lorem ipsum')

One possible drawback of this technique is that it essentially doubles your storage requirements. An alternative in this case is not duplicating the field(s), but pushing them to a search table (so no content field in the content table - it sits in the content_search table and is joined as required. You would them lose the ability to use triggers to manage the data as well as losing transactions on this data.

The End

* Seriously, in any non-trivial application, transactions should be a minimum requirement
** I need to find the reference, but there’s a LiveJournal scaling document that insists that the use of InnoDB is a minimum for scaling MySQL effectively.

This entry was posted on Monday, July 9th, 2007 at 12:01 pm and is filed under Development, MySQL, Programming, Snippets. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.