Foreign keys for myISAM and InnoDB tables

Question

I have a DB table that is myISAM, used for fulltext searching. I also have a table that is InnoDB. I have a column in my myISAM table that I want to match with a column in my InnoDB table. Can that be done? I cant seem to work it out!

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Foreign keys definitions are subject to the following conditions:

Both tables must be InnoDB tables and they must not be TEMPORARY tables.

So, I'm afraid you wont be able to achieve what you want done.

Answer 2

I would recommend altering your DB architecture such that you have one set of tables designed with data integrity for writing (all InnoDB), and a second set designed for search - possibly on a different box, and possibly not even using MySQL, but maybe a search server like Solr or Sphinx, which should outperform a fulltext MySQL table. You could then populate your search DB periodically from your write DB.