DB design and indexing 1

[leegold2]

Say I'm searching a table for key words w/eg"....LIKE '%keyword%'", I intend to index for speed. What is better(?) putting all the keywords for a record in one text-type field,
id keywds(text field)
1 frog foo bar perl
2 frog php happy day

vs.
give each each keyword it's own record ie. varchar field,
id keywds(varchar field)
1 frog
1 foo
1 bar
1 perl
2 frog
2 php
2 happy
2 day

Of course the id field relates to other tables holding actual content. The 2nd way seems more granular and maybe faster? What's better?

Thanks.

 

[ericbrunson]

The second case scenario would be better if you are using like 'term%'. If you use like '%term%' you invalidate all your indexes, do it doesn't matter.

Note, indexes break even at about 100-200 records, before that you actually take a penalty with an index.