Full text indexing on a column in a foreign table

Question

My entire database is in INNDB. I love the features, hands down. However it doesn't allow full text indexing on TEXT-type columns. So I have to take my current TEXT column from my main table (INNODB) and create a MYISAM table and reference back to the original table. But because MYISAM doesn't allow FK constraints I realize I've created a potential weakness. If the original table index changes it won't cascade down into the MYISAM table. Vice versa if I create a FK link from the original table to the MYISAM table, and the MYISAM row is deleted, then I have linked to a nonexistent entry. The data consistency check is simply not there.

In short, INNODB got me too comfortable and dependent on FK constraints for my own good.

Answer 1

I would consider not using the MyISAM fulltext indexing at all, and instead using a proper search engine alongside your db. Lucene/Solr, sphinx and xapian seem to be the leading choices (I've only used Lucene/Solr myself).

see this question for more :)

edit: also this question.

Answer 2

If you are using some sort of framework, the framework can control the referential integrity for you. CakePHP does a nice job of this with their Model classes.