Foreign keys for myISAM and InnoDB tables

Question

I have a DB table, which is myISAM used for full-text search. I also have a table which is InnoDB. I have a column in my myISAM table that I want to map to a column in my InnoDB table. It can be done? I can't seem to fix this!

Answer 1

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

Foreign key definitions are subject to the following conditions:

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

So, I'm afraid that you will not achieve what you want.

Answer 2

I would recommend changing the architecture of the database so that you have one set of tables designed to ensure the integrity of the data for writing (all InnoDB), and a second set designed to look up - perhaps in a different field and, perhaps, not even using MySQL, but perhaps a search engine such as Solr or Sphinx, which should outperform MySQL's full-text table. You can then populate your search database from your record database.