MySQL Forums
Forum List  »  Full-Text Search

Re: Full text and support for foreign keys?
Posted by: Rick James
Date: August 18, 2010 09:04PM

"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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Full text and support for foreign keys?
3358
August 18, 2010 09:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.