Re: Full text and support for foreign keys?

[Rick James]

"three columns in publication table" -- have one extra MyISAM table those three columns and the publication_id. Make a FULLTEXT index on the 3 columns, plus an index on the id. Ditto for the other tables.

Then tie them together using something like this:

SELECT ...
   FROM pub_main pm
   JOIN ( SELECT publication_id FROM pub_text WHERE MATCH(...) AGAINST ...) pt
        ON pt.publication_id = pm.publication_id
   JOIN copy_main cm
   JOIN ( ... FROM copy_text ...) ct ON ...
   ...
;

I usually avoid subqueries, but in this case, it may be the only way to efficiently trick it into using the FULLTEXT indexes.