Fetch related rows from tags

[mpw]

I'm using a 2 table tagging system for articles (myisam) and I'm trying to get related articles based on the tags and category_id of the article currently being viewed.

What would be the optimal query to achieve this? I only seem to be able to find answers for 3 table tag systems.

tags:

`article_id` int(11) NOT NULL DEFAULT '0',
`tag` varchar(99) NOT NULL DEFAULT '',
  PRIMARY KEY (`article_id`,`tag`),
  KEY `tag_ix` (`tag`)

articles:

`article_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`content` text NOT NULL,
`category_id` int(11) NOT NULL,
`tags` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`article_id`), 
KEY `category_id` (`category_id`)

Thanks for any help.

[K. Wolfe]

Remove tags from the articles table. You'll then only store the tags for each article in the tags table:

select
a.tag, a.article_id, c.article_title
from
tags a
inner join tags b on a.tag = b.tag and b.article_id <> a.article_id
inner join articles c on b.article_id = c.article_id
where
a.article_id = $currentArticleId

[mpw]

Thanks Kyle. I've added to the WHERE clause: AND dl.category_id = $categoryId and this makes the query go from 0.001 to 0.8 seconds. I really need it to fetch related articles based on the category ID of the current article, is there a better way of doing this?

[K. Wolfe]

Adding category id will solve your problem. You'll need to create an index if you want to improve query time.