Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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