I realised this morning as I played with my unit tests, based on yesteday’s post on Transactional Full-Text Search in MySQL that there is some potential for bad data. MySQL is smart enough to not create duplicates in the search table. If there’s an existing entry (in the case where a delete has not removed an old shadow-copy of the data) - MySQL will simply update the values. However, if there is no entry (because of some error when the content was created) MySQL is not quite smart enough to create one.
We can, however, do this in our trigger:
DELIMITER //;
CREATE TRIGGER content_update_search AFTER UPDATE ON pages
FOR EACH ROW BEGIN
IF (SELECT content_id FROM content_search WHERE content_id = NEW.id) IS NOT NULL THEN
UPDATE content_search SET title = NEW.title, content = NEW.content WHERE content_id = NEW.id;
ELSE
INSERT INTO content_search (content_id, content) VALUES (NEW.id, NEW.content);
END IF;
END//
If we find an existing entry, we update, if no entry exists, we create one.
An anonymous reader (thanks) also pointed out that if data storage is an issue, you can strip the stopwords from the data - MySQL ignores words less than four characters long as well as whole list of longer common words.