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,