title: Mysql Inner Join with OR condition - Query optimization
body: <p>Here's my query </p>
  
  <pre><code>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
  </code></pre>
  
  <p>This query works fine gets me the expected results but its very slow due to the OR condition on inner join. Here's what i tried to make it better.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But i'm not sure thats the best way to go.</p>
  
  <p>Here's the explain plan</p>
  
  <pre><code>    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      &lt;derived2&gt;             ref     &lt;auto_key0&gt;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               &lt;auto_key0&gt;                         4        univarie_uni_db.t.webinar_ref_id      36  Using index                                         
       1  PRIMARY      &lt;derived3&gt;             ref     &lt;auto_key0&gt;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               &lt;auto_key0&gt;                         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)  
  </code></pre>