Tags and MySQL - Speed and Usability

So I am trying to build a website which requires the use of tagging (you know, enter keywords that describe this page).

The tags are going to be eventually stored in (a) rows(s) MySQL (and php will be storing them, just an extra useless piece of information), we -my team- is trying to figure out which way is the fastest way to store tags:

1. Have 4 separate columns in the relevant SQL table (considering we are limiting tags at 4, this i a perfectly viable solution)
2. Have 1 column which is a comma-delimited list of tags (and then we can choose to change the limit)

Essentially, we are wondering which way is faster, considering that we are going to need to be able to do the following with tags:

• Search for single tag, or multiple tags
• In search only match a full match (ie the tag "truck" should not match the tag "firetruck")
• Block tags (ie user can search for "landscape -waterfall")

Thank you for any advice.

开发者_Go百科P.s. I am aware of this post, no it doesn't help at all

Assuming you have a Foo table with your main content that you want to tag, I'd recommend you add a Tag table. Tag has an ID, the tag string, and maybe a description field where you can describe what the tag actually means.

Then create a join table called FooTag that has a primary key ID column, the ID of the Foo record you're tagging, and the ID of the tag you're tagging it with. Foreign key relationships to Foo and to Tag for integrity.

Now you can find all the records for 1 through N tags at a time, you can find all the tags a Foo is tagged with, and so on. And you're not limited to four tags per Foo. And you don't have to specify four different columns in any query to figure out what tags are in play.