mysql: finding rows that have multiple tags and the same id

Question

I'm having an issue figuring the mysql to find links that have two specific 'tags' and the same 'hashid' when doing a JOIN of two tables

Assume my tables look like this:

Links

md5     url         title   numberofsaves
-----------------------------------------
a0a0    google.com  foo     200
b1b1    yahoo.com   yahoo   100

Tags

 md5    tag
 ---------------
 a0a0   awesome
 a0a0   useful
 a0a0   cool
 b1b1   useful
 b1b1   boring

I want to return rows that have tags of BOTH 'useful' and 'awesome'

The current (working/ fast) query for finding links by 1 tag:

SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 WHERE tags.tag = 'useful' ORDER BY links.numberofsaves DESC LIMIT 20

After reading an article I tried to use the following:

SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 GROUP BY tags.md5 HAVING SUM(tags.tag='useful') AND SUM(tags.tag='awesome') ORDER BY links.numberofsaves DESC LIMIT 20

This does work but it is so unbelievably slow as to be unusable.

Anyone know the solution?

Answer 1

The type of problem is called Relational Division

SELECT  a.md5, 
        a.url,
        a.title
FROM    Links a
        INNER JOIN Tags b
            ON a.md5 = b.md5
WHERE   b.Tag IN ('awesome', 'useful') -- <<== list of desired tags
GROUP   BY a.md5, a.url, a.title
HAVING  COUNT(*) = 2                   -- <<== number of tags defined

• SQLFiddle Demo
• SQL of Relational Division

OUTPUT

╔══════╦════════════╦═══════╗
║ MD5  ║    URL     ║ TITLE ║
╠══════╬════════════╬═══════╣
║ a0a0 ║ google.com ║ foo   ║
╚══════╩════════════╩═══════╝