带有OR条件-查询优化的Mysql内部连接
带有OR条件-查询优化的Mysql内部连接
提问于 2014-10-09 07:00:50
这是我的疑问
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = ter.user_ref_id OR tum.user_id = tea.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_id这个查询可以很好地获得预期的结果，但由于内部联接上的OR条件，它的速度非常慢。我试着让它变得更好。
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN (
SELECT event_ref_id, user_ref_id
FROM tbl_event_registrants GROUP BY user_ref_id
) ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN (
SELECT event_ref_id, user_ref_id
FROM tbl_event_attendees GROUP BY user_ref_id
) tea ON tea.event_ref_id = t.webinar_ref_id
-- LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
-- LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = ter.user_ref_id OR tum.user_id = tea.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_id但我不确定这是最好的方法。
这是解释计划
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- --------------------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- ------- -------------------------------- ------ ----------------------------------------------------
1 PRIMARY t ref FK_di_webinar_direfid FK_di_webinar_direfid 2 const 1 Using temporary; Using filesort
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 4 univarie_uni_db.t.webinar_ref_id 36 Using index
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 4 univarie_uni_db.t.webinar_ref_id 11 Using index
1 PRIMARY tum ALL PRIMARY,UNQ_tbl_user_master_LoginRefId,idx_user_master_membership_ref_id,FK_user_master_board_ref_id,FK_tbl_user_master_school_id,FK_tbl_user_master_profile_statusID,FK_tbl_user_master_payment_statusID,FK_tbl_user_master_url_ref_id,idx_tbl_user_master_FirstName,idx_tbl_user_master_LastName,fk_tbl_user_master_CityRefId,fk_tbl_user_master_StateRefId,fk_tbl_user_master_CountryRefId,fk_tbl_user_master_EthnicityRefId,fk_tbl_user_master_FamilyIncomeRefId,fk_tbl_user_master_ScholarshipImportanceRefId,fk_tbl_user_master_ReasonStudyAbroadRefId,fk_tbl_user_master_PrimaryFinancingRefId,fk_tbl_user_master_CitizenRefId,Idx_tbl_user_master_CriticalPercent,Idx_tbl_user_master_PresentClass,Idx_tbl_user_master_ClassStatus,srinivas_test (NULL) (NULL) (NULL) 35641 Using where; Using join buffer (Block Nested Loop)
3 DERIVED tbl_event_attendees index fk_tbl_event_attendees_UserRefId fk_tbl_event_attendees_UserRefId 5 (NULL) 845 (NULL)
2 DERIVED tbl_event_registrants index fk_tbl_event_registrants_UserRefId fk_tbl_event_registrants_UserRefId 5 (NULL) 3568 (NULL) 回答 3
Stack Overflow用户
回答已采纳
发布于 2014-10-09 08:06:39
在您的评论中，您说，您不必向每个用户显示一个示例Webinar。那么您甚至不必从Webinar表中进行选择。毕竟，您只是想让所有在Webinar中有角色的用户。因此，从tbl_user_master中选择并确保在两个表之一tbl_event_registrants和tbl_event_attendees中提到了用户id。
select first_name, last_name
from tbl_user_master
where user_id in
(
select user_ref_id
from tbl_event_registrants
union all
select user_ref_id
from tbl_event_attendees
);然而，MySQL在IN子句和UNION查询上可能非常慢。以下是两个存在条款的相同之处：
select first_name, last_name
from tbl_user_master tum
where exists
(
select *
from tbl_event_registrants ter
where ter.user_ref_id = tum.user_id
)
or exists
(
select *
from tbl_event_attendees tea
where tea.user_ref_id = tum.user_id
);如果您只想向用户展示参与所有Webinars的用户，那么您必须找到网络研讨会的总数，并与与用户关联的webinars数量进行比较。
select first_name, last_name
from tbl_user_master tum
where
(
select count(distinct event_ref_id)
from
(
select event_ref_id
from tbl_event_registrants
where user_ref_id = tum.user_id
union all
select event_ref_id
from tbl_event_attendees
where user_ref_id = tum.user_id
)
) = (select count(*) from di_webinar);编辑:下面是与联接相同的内容：
select tum.first_name, tum.last_name
from tbl_user_master tum
join
(
select user_ref_id, event_ref_id
from tbl_event_registrants
union
select user_ref_id, event_ref_id
from tbl_event_attendees
) ref on ref.user_ref_id = tum.user_id
group by tum.user_id
having count(*) = (select count(*) from di_webinar);Stack Overflow用户
发布于 2014-10-09 08:17:12
试试这个：
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = tea.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_id
union
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = ter.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_idStack Overflow用户
发布于 2014-10-09 08:45:18
试试这个：
SELECT tum.user_id, tum.first_name, tum.last_name
From tbl_user_master tum
Left Join (
Select ter.user_ref_id as kTER, tea.user_ref_id as kTEA
FROM di_webinar t
LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
WHERE t.di_ref_id ='93'
GROUP By ter.user_ref_id
) a On a.kTER = tum.user_id or a.kTEA = tum.user_id
GROUP BY tum.user_id我的建议，子查询中的表，作为表之前
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/26272197
复制相关文章
相似问题
腾讯云开发者
