mysql fulltext search table design

Question

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

Answer 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.