Blog post tags in MySQL

I'm working on a blog-like system and I'm planning on adding tags to posts; for example, if I make a blog post about an Independent journalist being allowed access to Iran, I might tag it with independent, journalist and Iran.

I have a single column in my posts database called tags which contains the tags in a VARCHAR like this: "independent, journalist, Iran".

How can I pull all the rows from the table with the tag independent and put as little strain on the server as possible? Should I use something other than VARCHAR?

 

use an extra table only for the tags, and an other table to save which tag is used for which posting, like this:

create table tags(
tagid INT NOT NULL AUTO_INCREMENT ,
tag VARCHAR( 40 ) NOT NULL ,
PRIMARY KEY ( tagid ),
INDEX (tag)
);

create table tagstoposts(
tagid INT NOT NULL,
postid INT NOT NULL,
UNIQUE t2p ( tagid, postid ),
INDEX (postid)
);

create table posts(
postid INT NOT NULL AUTO_INCREMENT ,
posttext TEXT NOT NULL,
...
...
PRIMARY KEY ( postid )
);


When you save all tags in one field in the posts database you have to make a query with LIKE statement, that isn't really performant...

with these 3 tables, you can make fast querys...

to get all tags for a post:
select tag.tags as tag from tags, tagtoposts where tagid.tags=tagid.tagtoposts and postid.tagtoposts = postid;

all posts with tag Indipendent:
select postid.tagtoposts as postid from tags, tagtoposts where tagid.tags=tagid.tagtoposts and tag.tags = "Indipendent";

all tags:
select tag from tags;

 

Hey Sudoku-Master, thanks for the help. I'll try to integrate this system as soon as I can. Unfortunately, it'll take a while to transfer the tags from a VARCHAR into separate rows in a different table for the posts I've already created, but I suppose it will be worth it.

However, is this really the most efficient way to do this? For example, I can already see a flaw; the tags table will contain many duplicates. I should search first to see if the tag already exists, and then use the existing tag ID instead. However, this is yet another query, which will probably put more strain on the server, especially if I receive more hits.

One of my primary focuses here is scalability. I want to make sure that the script runs just as fast whether I get 100 uniques or 10,000 uniques.

 

yes, every tag should be unique in the table, maybe you use an autocomplete for the tags over ajax.. ;-)

these solution with 3 tables is the best and performatest way to do this....