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

Question

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?

Answer 1

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.