从连接两个表的查询中获得不同计数的最佳方法(多种连接可能性) [英] Best way to get distinct count from a query joining two tables (multiple join possibilities)
问题描述
我有2个表,表 Actions
&表 Users
。 操作
-> 用户
是多一个
关联。 / p>
表操作
(具有数千行)
- id
- uuid
- name
- type
- created_by
- org_id
表 Users
(最多一百行)
- id
- 用户名
- org_id
- org_name
我正在尝试使用 WHERE
子句获得最佳联接查询以获取计数。我需要表 Actions
与 org_name $ c的表中不同的
created_by
的计数表 Users
中包含 myorg的$ c>。另外,( Actions.created_by
= Users.username
)
<我目前有以下查询(产生预期结果),不知道哪个更好,是否可以进一步优化?
查询1:
选择计数(与Actions.created_by不同)来自Actions的
加入了Users.org_id上的
用户
= Actions.org_id
其中Users.org_name如'%myorg%';
查询2:
select count(distinct Users.username)Users中的
加入
Actions.created_by上的Actions
= Users.username
其中Users.org_name如'%myorg %';
最快的方法是修改第二个查询:
从用户中选择计数(*)
u
存在(从Actions中选择1个
其中a.created_by = u.username
)
和u.org_name如'%myorg%';
然后最好的索引来自 actions(created_by)
。
I have 2 tables, table Actions
& table Users
. Actions
-> Users
is many-one
association.
Table Actions
(has thousands of rows)
- id
- uuid
- name
- type
- created_by
- org_id
Table Users
(has a max of hundred rows)
- id
- username
- org_id
- org_name
I am trying to get the best join query to obtain a count with a WHERE
clause. I need the count of distinct created_by
s from table Actions
with an org_name
in Table Users
that contains 'myorg'. Also, (Actions.created_by
= Users.username
)
I currently have the below queries (producing expected results) and wonder which is better and if it can be optimized further?
Query 1:
select count(distinct Actions.created_by)
from Actions join
Users
on Users.org_id = Actions.org_id
where Users.org_name like '%myorg%';
Query 2:
select count(distinct Users.username)
from Users join
Actions
on Actions.created_by = Users.username
where Users.org_name like '%myorg%';
The fastest approach is to modify the second query:
select count(*)
from Users u
where exists (select 1
from Actions a
where a.created_by = u.username
)
and u.org_name like '%myorg%';
Then the best index is on actions(created_by)
.
这篇关于从连接两个表的查询中获得不同计数的最佳方法(多种连接可能性)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!