Fulltext boolean search on InnoDB

On different MySQL table engines different rules apply. For example on MyIsam you can have Full Text indexes (thus enabling full text search) but you are giving up foreign keys.

I searched the way to get both on out-of-the-box mysql but failed (this can be enabled with use of sphinx mysql plugin). The best thing I could think of were triggers.

What are triggers?

Trigger is simple peace of sql code that is executed when predefined event happens. Most commonly they are used after insert or before insert.

General Idea

I we can’t have one table with full text and referential integrity then we need two tables one InnoDB and one MyIsam. It would be really stupid to create two identical tables, so I created new MyIsam table with only those columns I wanted to search and key which connects these two tables.
After this I needed a After Insert Trigger to execute so both tables would have identical content. Problem with this is that MySQL introduced triggers in version 5, which is still considered to be an very exotic peace of software.

Here is a sample Before Insert trigger:

mysql> CREATE TRIGGER test
-> AFTER INSERT ON t
-> FOR EACH ROW
-> BEGIN
->   INSERT INTO test2 SET column1 = NEW.column1;
->   INSERT INTO test3 SET column1 = NEW.column1 + 4;
->   END;//
Query OK, 0 rows affected (0.00 sec)