mysql - 优化 MySQL 查询

Question

我有一个包含 3 个表的 MySQL 数据库

Keywords
id, keyword, projects_id

Year
results_id*, jan, feb, mar, ..., nov, dec

Results
id, keywords_id*, country, user

star = foreign key

我需要查找项目 1 中存在的以及项目 2,3 和 4 中存在的任何关键字产生的年度总交易量，并按国家/地区和用户分组。

这就是我目前所拥有的

SELECT SUM(y.january + 
           y.february +
           ...
           y.december) AS 'sum',
       r.country , r.user
       FROM results r, year y           
       WHERE y.results_id = r.id 
             AND keywords_id IN(
                                   SELECT DISTINCT k.id
                                   FROM keywords k 
                                   JOIN keywords kk ON k.keyword = kk.keyword
                                   WHERE k.projects_id = 1 
                                   AND kk.projects_id IN (2,3,4)
                                   )
       GROUP BY country, user;

我的逻辑是这样的:

1. 通过 JOIN 查找属于项目 1 且同时位于项目 2、3、4 中的所有关键字的 ID。
2. 然后查找包含任何关键字 ID 的 keywords_id 条目的任何结果
3. 最后将年份表中的所有卷添加到一起，并按国家/地区和用户分组

我尝试了第一步的子查询(括号内的那个)，它返回超过 1700 个关键字

但是当我在 30 分钟后尝试整个关键字时，我没有得到任何结果。

如何重写查询以加快速度，或者如果我做错了什么？

提前非常感谢

Answer 1

这是我的写法。第一个查询通过项目 1 关键字的限定符获取所有 4 个项目 1、2、3 和 4 中必须存在的所有关键字，并连接到 2、3 和 4 的其他项目。如果您想要至少项目 1 和任意 2， 3或4，我会稍微改变一下。

然后，才连接到结果和年份表。现在，帮助优化。您的关键字表应该有一个索引(id，projects_id)。结果表应在 (keywords_id, Country, user ) 上有一个索引 - 用于在分组中提供帮助的国家/地区和用户。

select STRAIGHT_JOIN
      r.country,
      r.user,
      SUM( y.january + y.february + y.march
         + y.april   + y.may      + y.june
         + y.july    + y.august   + y.september
         + y.october + y.november + y.december ) as AllMonths
   from 
      ( SELECT k.id
           FROM keywords k
              JOIN keywords k2 on k.id = k2.id AND k2.project_id = 2
              JOIN keywords k3 on k.id = k3.id AND k3.project_id = 3
              JOIN keywords k4 on k.id = k4.id AND k4.project_id = 4
           where 
              k.project_id = 1 ) KeywordsInAll             
         JOIN results r
            ON KeywordsInAll.ID = r.keywords_id
            JOIN `year` y
               ON r.id = y.results_id
   group by 
      r.country,
      r.user

我将内部查询更改为“至少从项目 1 开始使用关键字”...如果项目 1 只有 15 个关键字，那么查询 10,000 个关键字就没有意义(夸张，但只是示例)

根据您发布的评论，我只需将预查询更改为几乎与您的完全一样，但保持在第一位并保留“STRAIGHT_JOIN”

      ( SELECT DISTINCT k.id
           FROM keywords k
              JOIN keywords k2 on k.id = k2.id 
               AND k2.project_id IN (2, 3, 4 )
           where 
              k.project_id = 1 ) KeywordsInAll