CODEWITHSUNDEEP
mysqlsqlsql-match-allrelational-division
evodevo
evodevo

Reputation: 509

Efficient way to get all articles with a set of tags in MySQL

I need an efficient way to select all articles with at least tags "Tag1" and "Tag2". This is the standart way with the following database schema:

articles(id, title)
article_tag(articleid, tagid)
tag(id, name)

SELECT a.*
FROM   article a
     INNER JOIN (SELECT   at.articleid
               FROM     article_tag at
                        INNER JOIN article a
                          ON a.id = at.articleid
                        INNER JOIN tag t
                          ON t.id = at.tagid
               WHERE    t.name IN ("Tag1","Tag2")
               GROUP BY at.articleid
               HAVING   Count(at.articleid) = 2) aa
     ON a.id = aa.articleid

Is there a more efficient way performance wise?

Upvotes: 2

Views: 950

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

I would expect this query to be faster (tailored to your conditions):

SELECT a.*
FROM  (
    SELECT at.articleid AS id
    FROM   article_tag at
    JOIN   tag t ON t.id = at.tagid
    WHERE  t.name = 'Tag1'
    ) a1
JOIN  (
    SELECT at.articleid AS id
    FROM   article_tag at
    JOIN   tag t ON t.id = at.tagid
    WHERE  t.name = 'Tag2'
    ) a2 USING (id)
JOIN article a USING (id);

However, the most important part here are indexes. The primary keys will be indexed automatically (I assume):

  • articles on (id)
  • article_tag on (articleid, tagid) -- multi-column index
  • tag on (id)

In addition these will help your case:

  • tag on (name)
  • article_tag on (tagid)

This is a special case of relational division. Here is an extensive coverage of your case exactly. You will be surprised about how many good ways there are.

Upvotes: 3

Related Questions