Mysql Inner Join with OR 条件 - 查询优化

Question

这是我的问题

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)  

Answer 1

在您的评论中，您不必为每个用户展示一个示例网络研讨会。那么您甚至不必从网络研讨会表格中进行选择。毕竟，您只需要参与网络研讨会的所有用户。因此，从 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 查询上可能非常慢。所以这里有两个 EXISTS 子句:

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
);

如果您只想显示参加了所有 网络研讨会的用户，那么您必须找到网络研讨会的总数，并将其与与用户关联的网络研讨会的数量进行比较。 p>

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);