MYSQL优化加入UNION SELECT查询

Question

我已经重写了几次这个查询(现在是星期一)，试图找到获得我所需数据的最有效的方法，但是我不确定我现在是否正确地处理了它。

总结问题的；

用户有两组标记(key_terms，project_terms)，在users和tags表之间都有一个链接表。

我想取出任何一个表中有指定标签的用户。理想情况下，它还应该包括与用户“最相关”的标签--但让我们暂时搁置它。

用户

| id | name |
| 1  | dayjo |
| 2  | stackoverflow |

标签

| id | tag  |
| 1  | tag1 |
| 2  | tag2 | 

user_key_term

| user_id | tag_id |
| 1       | 1      |
| 1       | 2      |
| 2       | 1      |

project_key_term

| user_id | tag_id | 
| 1       | 3      |
| 2       | 3      |

我想要查询的是命名标记，也就是说，如果我搜索"tag1“，两个用户都应该返回，但是如果我搜索"tag2”，则只应该返回用户1。

我的解决方案

我试着选择用户，并加入两次标签(每个链接表一次)，这似乎正常，但不确定这是最好的方式，并没有找到如何获得最相关的标签。

SELECT t1.tag, t2.tag as most_relevant_tag, users.* FROM users
LEFT JOIN user_key_term ON user_key_term.user_id = users.id
LEFT JOIN tags t1 ON user_key_term.tag_id = t1.id
LEFT JOIN project_key_term ON project_key_term.user_id = users.id
LEFT JOIN tags t2 ON project_key_term.tag_id = t2.id
WHERE t1.tag IN ('tag1','tag2') OR t2.tag IN ('tag1','tag2')
GROUP BY users.id;

我的下一次尝试是联合选择，但这一次感觉很肮脏；

SELECT users.*  FROM 
    `users`
    INNER JOIN (
        SELECT project_key_term.user_id, tags.id, tags.tag FROM project_key_term
        JOIN tags ON tags.id = project_key_term.tag_id AND tags.tag IN ('tag1')
        UNION ALL
        SELECT user_key_term.user_id,tags.id, tags.tag  FROM user_key_term
        JOIN tags ON tags.id = user_key_term.tag_id AND tags.tag IN ('tag1')
        ) tags ON tags.user_id = users.id
    WHERE tags.tag IN ('tag1')
    GROUP BY users.id;

但

我试着在这两个查询上运行EXPLAIN来查看哪一个是最好的，但它并没有显示出对我特别有用的任何东西。特别是因为目前表中的数据不多，可能会有成百上千个标签。

任何关于“正确”或最佳实践方法的帮助都将是很棒的！

Answer 1

union查询可以简化为：

SELECT users.*  
FROM users
INNER JOIN (
        SELECT user_id,tag_id 
        FROM project_key_term
        UNION ALL
        SELECT user_id,tag_id 
        FROM user_key_term
        ) alltags ON alltags.user_id = users.id
INNER JOIN tags t on t.id = alltags.tag_id
where t.tag IN ('tag1')

编辑:获取最相关的标记

 SELECT score, t.tag, users.* 
 FROM users
 INNER JOIN (select user_id, tag_id, count(*) as score 
             from (SELECT user_id,tag_id 
                   FROM project_key_term
                   UNION ALL
                   SELECT user_id,tag_id
                   FROM user_key_term
                   ) alltags
             group by user_id,tag_id) tagcounts ON tagcounts.user_id = users.id
 INNER JOIN tags t on t.id = tagcounts.tag_id
 where t.tag IN ('tag1','tag2','tag3')
 ORDER BY score DESC