Geek Answers Handbook

Mysql: finding strings that have multiple tags and the same identifier

I had a problem with mysql calculation to find links that have two specific "tags" and the same "hashid" when doing JOIN from two tables

Suppose my tables look like this:

References

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 strings with BOTH tags "useful" and "awesome"

The current (working / quick) request to search for links by tag 1:

 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 the 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

It works, but it is incredibly slow to be unusable.

Does anyone know a solution?

+4
source share
1 answer

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

OUTPUT

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

More articles:


All Articles