• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

MySQL fulltext search joining tables

Hi,

I have a fulltext search on one of my tables but I want to also search on another table that is linked to another table (I know that doesn't make any sense yet)

I have 3 tables

tblblogarticle
articleID
posterName
articleTitle
articleContent

tblarticletags
articleID
tagID

tbltags
tagID
tagName

I have a fulltext search running on the tblarticle table that looks like this:
mysql_query('SELECT articleID, posterName, articleTitle, articleContent FROM tblblogarticle WHERE MATCH(articleTitle, articleContent) AGAINST ("'.$phrase.'"));

This is working ok, but I woud like to expand my search to include the article tags associated with each article.  The link is each article has several entries in the tblarticletags table that stores the articleID and tagID fields.  The tbltags table contains the tagID and tagName.  I want to search on the tagName with reference back to the original article to display the correct search results.

I guess I am going to have to use joins to do this, but am not sure how to construct it?  Any advice is welcomed.

Kind Regards,

John
0
john-formby
Asked:
john-formby
1 Solution
 
johanntagleCommented:
Usually tags are exact matches so I assume there's no fulltext index on tbltags:

SELECT articleID, posterName, articleTitle, articleContent
FROM tblblogarticle
WHERE MATCH(articleTitle, articleContent) AGAINST ("'.$phrase.'")
OR articleID in (select articleID from tblarticletags artgs join tbltags tgs on (artgs.tagID=tgs.tagID) where tgs.tagName="'.$phrase.'" )

ensure that tbltags.tagName is indexed.

If you want fulltext for tagName, then just replace the filter with MATCH, though you might encounter performance problems with many MATCH filters.  You might want to restructure your database where all fields to be searched are in one table, so you can make one full text index for the combined columns.
0
 
john-formbyAuthor Commented:
Thanks for the help, that works perfectly.

Kind Regards,

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now