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!
Asked
Active
Viewed 5,294 times
2 Answers
14
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.
ssj1980
- 391
- 2
- 7
-
Thats what I thought. Thanks for your reply! – Becs Carter Nov 22 '12 at 03:49
3
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.
Steven Moseley
- 15,871
- 4
- 39
- 50
-
This is pretty easy to do with a `TRIGGER` if you can't find a way to do it in your ORM. – tadman Nov 22 '12 at 00:57