Mysql related articles

Question

Hi all,

I'm trying to make a query return 3 articles related to the one a user is reading. For this purpose I've made a tabel with a set of tags related to all articles (up to 5 tags pr. article).

• How do I construct my query the most effective way to return 3 related articles?
• Incase there is no related articles - how to return 3 random?

I hope someone can lead me in the right direction.

Thanks in advance!

CREATE TABLE IF NOT EXISTS `j_news` (
  `n_id` smallint(5) NOT NULL AUTO_INCREMENT,
  `n_title` varchar(94) COLLATE utf8_danish_ci NOT NULL,
  `n_text` text COLLATE utf8_danish_ci NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci AUTO_INCREMENT=25 ;


CREATE TABLE IF NOT EXISTS `j_news_tags` (
  `nt_id` mediumint(7) NOT NULL AUTO_INCREMENT,
  `nt_news_id` mediumint(7) NOT NULL,
  `nt_tag` varchar(24) COLLATE utf8_danish_ci NOT NULL,
  PRIMARY KEY (`nt_id`),
  KEY `nt_news_id` (`nt_news_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci AUTO_INCREMENT=1 ;

Answer 1

If you are really considering performance I would suggest you read up this question I had sometime ago. It deals with large databases and has benchmarks on different queries (all dealing with tags).

To shorten it out the SQL would be like this:

SELECT DISTINCT p . * 
FROM projects p
JOIN projects_to_tags pt ON pt.project_id = p.num
JOIN projects_to_tags x ON x.tag_id = pt.tag_id
AND x.project_id = x -- x is your project ID number