Geek Answers Handbook

MySQL - search for rows matching all rows from a joined table.

Table 1: Tracks

Table 2: List of words

Table 3: N: M Track contains words (track words)

Find all the tracks that have all the words.

currently the request looks like this:

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 uses all the necessary indexes:

 +----+-------------+-------+--------+-----------------------+---------+---------+----------------+---------+-------------+ | 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 request takes a lot of time. Any suggestion to expedite the request?

0
source share
3 answers

Your set of problems is very similar to the set of tags for an element such as StackOverflow or Del.icio.us.

Article Tags: database schemas offer several solutions, including the idea of ​​@ ChssPly76.

+3
source

There is no point in left associations if you are looking only for tracks that have all the words. I assume that the combination ( trackid , wordid ) 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 will be useful for indexes in wordlist (word), trackwords (trackid, wordid) and track (id).

+5
source

It will probably be faster if you split this into two queries. Firstly, combining words and track words so that you can use all the tracks you need. Then go back to the track table and do:

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

but based on the request, first of all you return t.id, which you already have from tw.trackid.

0
source

More articles:


All Articles