MySQL-从联接表中查找与所有行匹配的行 [英] MySQL - Find rows matching all rows from joined table

问题描述

表1:曲目

表2:单词表

表3:N:M音轨包含单词(音轨单词)

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

查找所有包含所有单词的曲目.

Find all tracks which have all the words.

当前查询如下:

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;

根据EXPLAIN的说明，它使用了所有必要的索引:

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?

推荐答案

您的问题集非常类似于为StackOverflow或Del.icio.us这样的项目存储标签的问题.

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

文章标签:数据库模式提出了几种解决方案，其中包括@ ChssPly76的想法.

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

这篇关于MySQL-从联接表中查找与所有行匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！