MySQL - Find rows matching all rows from joined table

Question

Table 1: Tracks

Table 2: Wordlist

Table 3: N:M Track has Words (trackwords)

Find all tracks which have all the words.

currently the query looks like:

SELECT DISTINCT t.id FROM track as t
Left Join trackwords as tw ON t.id=tw.trackid
Left Join wordlist as wl on wl.id=tw.wordid
WHERE 
wl.trackusecount>0 
group by t.id
HAVING SUM(IF(wl.word IN ('folsom','prison','blues'),1,0)) = 3;

Which according to EXPLAIN is using all indexes neccessary:

+----+-------------+-------+--------+-----------------------+---------+---------+----------------+---------+-------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref            | rows    | Extra       |
+----+-------------+-------+--------+-----------------------+---------+---------+----------------+---------+-------------+
|  1 | SIMPLE      | t     | index  | PRIMARY               | PRIMARY | 4       | NULL           | 8194507 | Using index | 
|  1 | SIMPLE      | tw    | ref    | wordid,trackid        | trackid | 4       | mbdb.t.id      |       3 | Using where | 
|  1 | SIMPLE      | wl    | eq_ref | PRIMARY,trackusecount | PRIMARY | 4       | mbdb.tw.wordid |       1 | Using where | 
+----+-------------+-------+--------+-----------------------+---------+---------+----------------+---------+-------------+

But the query takes ages. Any suggestion to speedup the query?

ChssPly76 · Accepted Answer · 2009-08-07 00:31:51Z

5

There's no point in left joins if you're only looking for tracks that have all the words. I'm assuming that (trackid,wordid) combination is unique in trackwords.

SELECT t.id
  FROM track as t,  trackwords as tw, wordlist as wl
 WHERE t.id=tw.trackid
   AND wl.id=tw.wordid
   AND wl.trackusecount>0 /* not sure what that is - you have it in your query */
   AND wl.word in ('folsom','prison','blues')
 GROUP by t.id
HAVING count(*) = 3

This query would benefit from indexes on wordlist(word), trackwords(trackid,wordid) and track(id).

answered Aug 7, 2009 at 0:31

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rufinus Over a year ago

your query is using a temptable which makes it 3 times slower. but nevermind, your having count() = 3 is a a good idea und brings me to: SELECT DISTINCT t.name, t.id FROM track as t Left Join trackwords as tw ON t.id=tw.trackid Left Join wordlist as wl on wl.id=tw.wordid WHERE wl.trackusecount>0 AND wl.word IN ('folsom','prison','blues') group by t.id HAVING Count(t.id) = 3 LIMIT 0,10; which is nearly 10times faster then my orginal query :)

ChssPly76 Over a year ago

It should not use a temp table if you have appropriate indexes defined. You'll have 3 rows in wordlist, index lookup on trackwords and index lookup on tracks

Rufinus Over a year ago

strange, in some cases your version is faster, in some cases mine is faster. (taking different words to test, query cache is strong in this case :-) btw. your solution is the same as mentioned on the link from thinkzig. so i guess its right :-)

Rufinus Over a year ago

Rufinus Over a year ago

gave the accepted answer to thinkzig, dont be mad, just giving a newb like me some points :-)

|

Leif Arne Storset · Accepted Answer · 2018-01-07 22:25:44Z

3

Your problem set is very much like that of storing tags for an item like StackOverflow or Del.icio.us does.

The article Tags: Database schemas proposes several solutions, among them @ChssPly76's idea.

edited Jan 7, 2018 at 22:25

Leif Arne Storset

9791 gold badge8 silver badges21 bronze badges

answered Aug 7, 2009 at 0:30

JamieGaines

2,0821 gold badge18 silver badges21 bronze badges

2 Comments

Rune Schjellerup Philosof Over a year ago

Except that the link is dead now.

Rufinus Over a year ago

i found the new link for it.

Robert Swisher · Accepted Answer · 2009-08-07 00:40:18Z

0

Would probably be faster if you broke this up into two queries. First, a join of the words and trackwords to net you all the trackids you need. Then go back to the track table and do:

WHERE t.id IN(...trackids here...)

but based on the query above all you're returning is t.id which you have from tw.trackid already.

answered Aug 7, 2009 at 0:40

Robert Swisher

1,30011 silver badges12 bronze badges

1 Comment

Rufinus Over a year ago

theoreticly right, but i shortend the SELECT part of the query to make it easier to understand :) i tried this with seperated queries, and sub selects, all versions were slower :(

Collectives™ on Stack Overflow

MySQL - Find rows matching all rows from joined table

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related