MySQL COUNT多个左连接

MySQL COUNT多个左连接 - optomization(MySQL COUNT of multiple left joins - optomization)

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

架构如下所示：

成员PK member_id

table1 PK tbl1_id FK member_id

table2 PK tbl2_id FK member_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的情况下多次运行查询。 问题是这个查询运行得很慢。 我得到了我需要的信息，但我想知道是否有人可以提出一种优化方法。 非常感谢任何建议。 非常感谢。

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.

The schema looks like this:

member PK member_id

table1 PK tbl1_id FK member_id

table2 PK tbl2_id FK member_id

table3 PK tbl3_id FK member_id

My query looks like this:

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

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.

最满意答案

你应该重构你的查询。 您可以通过重新排序查询收集数据的方式来执行此操作。 怎么样？

首先应用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 ;

BTW我将member m更改为SELECT * FROM member m WHERE member_id = 27 ，以防您需要有关成员27的任何信息。我​​还在每个结果中添加了IFNULL函数，以便在计数为NULL时生成0。

你需要绝对确定

member_id是成员表的主键 member_id在table1，table2和table3中编制索引

试一试 ！！！

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

Apply the WHERE clause first Apply JOINs last

Here is your original query:

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

Here is you new query

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 ;

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.

You need to make absolutely sure

member_id is the primary key of the member table member_id is indexed in table1, table2, and table3

Give it a Try !!!