多个左连接的 MySQL COUNT - 优化 [英] MySQL COUNT of multiple left joins - optomization

问题描述

我有一个查询，它通过使用 LEFT JOIN 和子查询从多个表中获取计数.这个想法是计算成员参与的各种活动.

I have a query that is getting counts from multiple tables by using a LEFT JOIN and subqueries. The idea is to get a count various activites a member has participated in.

架构如下所示:

会员PK会员_id

表1PK tbl1_idFK会员_id

table1 PK tbl1_id FK member_id

表2PK tbl2_idFK会员_id

table2 PK tbl2_id FK member_id

table3PK tbl3_idFK会员_id

table3 PK tbl3_id FK member_id

我的查询如下所示:

SELECT t1.num1,t2.num2,t3.num3
FROM member m
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num1
   FROM table1
   GROUP BY member_id
) t1 ON t1.member_id = m.member_id
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num2
   FROM table2
   GROUP BY member_id
) t2 ON t2.member_id = m.member_id
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num3
   FROM table3
   GROUP BY member_id
) t3 ON t3.member_id = m.member_id
WHERE m.member_id = 27

其中 27 是测试 ID.实际查询连接了三个以上的表，并且在更改 member_id 的情况下多次运行查询.问题是这个查询运行得很慢.我得到了我需要的信息，但我想知道是否有人可以提出一种优化方法.非常感谢任何建议.非常感谢.

Where 27 is a test id. The actual query joins more than three tables and the query is run multiple times with the member_id being changed. The problem is this query runs pretty slow. I get the info I need but I am wondering if anyone could suggest a way to optimize this. Any advice is very much appreciated. Thanks much.

推荐答案

您应该重构您的查询.您可以通过重新排序查询收集数据的方式来做到这一点.怎么样?

You should refactor your query. You can do this by reordering the way the query collects the data. How?

• 首先应用 WHERE 子句
• 最后应用 JOIN

这是您的原始查询:

SELECT t1.num1,t2.num2,t3.num3 
FROM member m 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num1 
   FROM table1 
   GROUP BY member_id 
) t1 ON t1.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num2 
   FROM table2 
   GROUP BY member_id 
) t2 ON t2.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num3 
   FROM table3 
   GROUP BY member_id 
) t3 ON t3.member_id = m.member_id 
WHERE m.member_id = 27 

这是您的新查询

SELECT
   IFNULL(t1.num1,0) num1,
   IFNULL(t1.num2,0) num2,
   IFNULL(t1.num3,0) num3
FROM
(
   SELECT * FROM member m 
   WHERE member_id = 27
) 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num1 
   FROM table1 
   WHERE member_id = 27
   GROUP BY member_id 
) t1 ON t1.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num2 
   FROM table2 
   WHERE member_id = 27
   GROUP BY member_id 
) t2 ON t2.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num3 
   FROM table3 
   WHERE member_id = 27
   GROUP BY member_id 
) t3 ON t3.member_id = m.member_id 
;

顺便说一句，我将 member m 更改为 SELECT * FROM member m WHERE member_id = 27 以防您需要有关成员 27 的任何信息.我​​还添加了 IFNULL 函数对每个结果产生 0，以防 count 为 NULL.

BTW I changed member m into SELECT * FROM member m WHERE member_id = 27 in case you need any information about member 27. I also added the IFNULL function to each result to produce 0 in case count is NULL.

你需要绝对确定

• member_id 是成员表的主键
• member_id 在 table1、table2 和 table3 中建立索引

试一试！！！

这篇关于多个左连接的 MySQL COUNT - 优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！