mysql - 解决多表左连接问题

Question

我有一个涉及很多表和左连接的 MySQL 情况，我在处理它时遇到了问题!

我会逐步简化它。

我要做的主要任务是连接两个表。第一个表包含项目，第二个表包含对项目执行的操作。我需要输出项目表的每一行(即使没有对它们执行任何操作)所以左连接似乎是解决方案:

select item.ID, count(action.ID) as cnt 
from item 
left join action on action.itemID=item.ID 
group by ID

下一步是我实际上只需要计算特定类型的项目。因为我不需要其他类型，所以我使用 where 子句将它们过滤掉。

select item.ID, count(action.ID) as cnt 
from item 
left join action on action.itemID=item.ID 
where item.type=3 
group by ID

现在事情变得有点复杂了。我还需要使用另一个表(信息)过滤掉一些项目。在那里，我不确定该怎么做。但是一个简单的连接和 where 子句就做到了。

select item.ID, count(action.ID) as cnt 
from (item, info) 
left join action on action.itemID=item.ID 
where item.type=3 and info.itemID=itemID and info.fr is not null 
group by ID

到目前为止一切顺利。我的查询有效，性能符合预期。现在，我需要做的最后一件事是根据另一个表(子操作)过滤掉一些操作(不计算它们)。这是事情变得非常缓慢并让我感到困惑的地方。我试过这个:

select item.ID, count(action.ID) as cnt 
from (item, info) 
left join (
           action join subaction on subaction.actionID=action.ID and subaction.type=6
          ) on action.itemID=item.ID 
where item.type=3 and info.itemID=itemID and info.fr is not null 
group by ID

此时，查询突然减慢了 1000 多倍。我显然做错了什么!

我尝试了一个几乎可以满足我需要的简单查询。唯一的问题是它不包括必须匹配操作的项目。但我也需要它们。

select item.ID, count(action.ID) as cnt 
from item, info, action, subaction 
where item.type=3 and info.itemID=itemID and info.fr is not null and 
      action.itemID=item.ID subaction.actionID=action.ID and subaction.type=6 
group by ID

有人对如何解决这样的问题有建议吗？有没有标准的方法来做到这一点？非常感谢!

编辑

实际上，我提交的最后一个查询几乎就是我所需要的:它不包括子查询，性能非常好，优化了我的索引的使用，易于阅读等。

select item.ID, count(action.ID) as cnt 
from item, info, action, subaction 
where item.type=3 and info.itemID=itemID and info.fr is not null and 
      action.itemID=item.ID subaction.actionID=action.ID and subaction.type=6 
group by ID

唯一不起作用的小事情是当 count(action.ID) 为 0 时不包括 item.ID。

所以我想我的问题实际上是我应该如何稍微修改上面的查询，以便它在 count(action.ID) 为 0 时也返回 item.IDs。据我所知，这应该不会改变性能和索引使用。只需包括那些额外的 item.IDs，计数为 0。

Answer 1

尝试如下加入(尝试在加入前先应用过滤条件):

      SELECT item.ID, count(action.ID) as cnt 
      FROM item JOIN info
           ON (item.type=3 AND info.fr is not null AND info.itemID=item.itemID)
           LEFT JOIN action 
           ON (action.itemID=item.ID)
           JOIN subaction 
           ON (subaction.actionID=action.ID and subaction.type=6)  
      GROUP by item.ID;

编辑:

      SELECT item.ID, count(action.ID) as cnt 
      FROM item JOIN info
           ON (item.type=3 AND info.fr is not null AND info.itemID=item.itemID)
           LEFT JOIN 
           (select a.* FROM action 
              JOIN subaction 
               ON (subaction.actionID=action.ID and subaction.type=6)) AS act 
           ON (act.itemID=item.ID)
      GROUP by item.ID;