MySQL full text searching multiple tables

Hello Experts,

I want to implement a full text search in a MySQL database. I have even asked a similar question before I decided to use full text search, but I realized that I need and that if I'm not wrong I cannot perform a full text search in multiple tables. So, what I'd like to have, is an opinion how I should proceed and if somebody has done something like that.

Imagine a database with articles that each articles can be written by 'n' authors, each article has 'n' categories ...

I'd like to perform a full text searching in all fields like:
 - article name(articles table)
 - article categories description (articles categories table linked through FK)
 - article authors name (articles authors table linked through FK)

And besides that I'd like to let the article available for readers to tag it. And I'm not really sure how should I implement a "tagging sytem" ... Should I put a tag field in the articles table, should I create another table just with tags ????

Can somebody shed light on this matter ? I'd appreciate it ...

I've also looked at Sphinx and Lucene, but as I understand I think I could achieve the results I want with MySQL full text. I'll have many records but not millions, that would require an external engine ...

Thanks in advance,

marcelocbfAsked:
Who is Participating?
 
AndyBeardCommented:
The problem with tagging, even though I am quite a vocal supporter of tagging as a concenpt, is that it sucks for all but them most common terms.

Thus is users end up all using the same tagging structures, thus web+design or webdesign then it works, but that is then something that is more like a category.

If a site owner uses tagging to supplement categories, if can work well. Huffington post goes crazy on it because each tag is then used as a title for a page, and as a search term.

But search term is the key... they are not using it only selecting content tagged with that term which is what true tagging folksonomy would suggest.

Take the tag you used for this "mysql lucene sphinx full-text search java"

That is a single tag that no other person is ever going to use, or the chances are extremely remote. This hapens all the time with tagging systems because some allow spaces and use comma delimeters, some no spaces but commas, some use signle words only, some use + between words etc.

What many sites do now is use Sphinx for tags

I believe Technorati uses Sphinx or similar for tag pages now
Craigslist is using Sphinx
I have seen some hints that WordPress.com is using Sphnx
Blogcatalog is using Sphinx
Article Snatch uses Sphinx

The latter 2 I help out a bit with SEO.

I don't get hands on with the sites, but I do know that when I have suggested they make quite significant changes to the search pages, the implementation time has been extremely rapid, and the results given by Sphinx have a good level of relevance.

I am writing this mainly in response to the part of your question regarding the tagging system.

All these sites effectively don't use pure tagging any more. A tag is just a hyperlinked predefined search term on the full text.
0
 
marcelocbfAuthor Commented:
Thanks AndyBeard,

I read your post and I'll consider Lucene and Sphinx again ...

I liked the idea before but I had not seen any difference of using MySQL full-text engine than Sphinx/Lucene 's , I'm reading the docs and thanks again for another point of view ...
0
 
Tony McCreathTechnical SEO ConsultantCommented:
One technique is to de-normalise your data:

Add a keywords field to your main table and fill it with all the text from the fields you wish to search. article name, all the category names, author name etc. Then you can do a full-text search just on that special field.

This can be good as you can process the searchable text as you add it to the field. e.g. fix up special characters which the full-text index has trouble with.

The other technique would be to add a separate expression in your query to capture  matches with the category:

SELECT ... WHERE MATCHING ...  OR c.category_name IN ('word1','word2','word3');
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.

All Courses

From novice to tech pro — start learning today.