marcelocbf
asked on
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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');
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');
ASKER
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 ...