0

I have been working on a Mysql db design which keyword based search seems to be necessary. Main table has two columns:

id | Title

For each entry, users will be required to enter 4 or 5 keywords. I think using a seperate table for keywords would be better. I would like to learn if there is any difference between these two designs in terms of performance, scalability and management. There will be millions of rows in the main table.

id | keywords
1  | 1kword1, 1kword2, 1kword3
2  | 2kword1, 2kword2, 2kword3

Or

id | keyword
1  | 1kword1 
2  | 1kword2 
3  | 1kword3
4  | 2kword1 
5  | 2kword2 
6  | 2kword3
1

If you do a comma delimited keyword set you will be forced to using a full text search solution to find records, in this case adding them to the main table would be beneficial. In the second case, if you are searching for exact keyword matches your performance will be faster and you can use a standard string based index rather than a full text one.

Using a dedicated search engine would be even better. There is a build (if it's not standard already) of Sphinx Search that works nicely with MySQL. Solr is also a nice solution.

Depends on your front end which might be easier to implement and manage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.