MySQL Forums
Forum List  »  Full-Text Search

Re: Full Text search
Posted by: Rick James
Date: September 07, 2011 09:52AM

> Y do we need to join tables, how is it helpful?

I will counter that with "Why did you split the fields into separate tables; how was that helpful?"

Then I will answer it: "Put the fields back into a single table!"

Well, there may be good reasons for splitting the data into multiple tables. So, let's discuss that.

It seems like a FAQ would be a list of Q & A, independent of news stories.

Then a list of news stories would be another table.

But I don't understand your "page" table?

Assuming just news and faq, then the UNION is the "right" way to search both, and come up with a single list that points to both news and faqs. The speed is whatever the speed is.

It is arguably clumsy and artificial to combine the text into a single table in order to simplify the query. It would go something like this:

TABLE news: news_id, misc fields
TABLE faq: faq_id, misc fields
TABLE bodies: type, id, body -- with FULLTEXT(body)
where the type points to either news or faq, and the id is either the news_id or faq_id. The SELECT would only hit `bodies`, but would have a clumsy JOIN to get to either news or faqs.

For further discussion, please provide
SHOW CREATE TABLE

Options: ReplyQuote


Subject
Views
Written By
Posted
3809
August 24, 2011 04:19AM
2034
August 25, 2011 05:32PM
1768
September 04, 2011 12:10PM
Re: Full Text search
2081
September 07, 2011 09:52AM


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.