CODEWITHSUNDEEP
mysqlruby-on-rails
Neo
Neo

Reputation: 5463

How to get tagged articles?

I have three tables:

articles(id int)
tags(id int, name varchar(255))
articles_tags(article_id, tag_id)

I want to find all articles which belong to tag1 and tag2, how do I write a good performance query with MySQL? Or is there a good way to design tables?

Now, I am using Sphinx for this, but Sphinx does not support aliving update index of order attributes column.

Upvotes: 0

Views: 89

Answers (1)

barsju
barsju

Reputation: 4446

SELECT a.* FROM articles a 
INNER JOIN articles_tags at ON a.id=at.article_id
INNER JOIN tags t ON t.id=at.tag_id
WHERE t.name IN ('tag1', 'tag2)
GROUP BY a.id
HAVING count(a.id) >= 2;

And you should probably add and index on your tag names.

Also you can consider using the tag name as the primary key of the tags table, getting rid of the tag id. That way you only need to join with the article_tags table:

SELECT a.* FROM articles a 
INNER JOIN articles_tags at ON a.id=at.article_id
WHERE at.tag_name IN ('tag1', 'tag2)
GROUP BY a.id
HAVING count(a.id) >= 2;

Upvotes: 2

Related Questions