sql - Mysql Inner Join with OR condition - Query optimization -

sql - Mysql Inner Join with OR condition - Query optimization -

here's query

select tum.user_id, tum.first_name, tum.last_name di_webinar t left bring together tbl_event_registrants ter on ter.event_ref_id = t.webinar_ref_id left bring together tbl_event_attendees tea on tea.event_ref_id = t.webinar_ref_id inner bring together tbl_user_master tum on tum.user_id = ter.user_ref_id or tum.user_id = tea.user_ref_id t.di_ref_id ='93' grouping tum.user_id

this query works fine gets me expected results slow due or status on inner join. here's tried create better.

select tum.user_id, tum.first_name, tum.last_name di_webinar t left bring together ( select event_ref_id, user_ref_id tbl_event_registrants grouping user_ref_id ) ter on ter.event_ref_id = t.webinar_ref_id left bring together ( select event_ref_id, user_ref_id tbl_event_attendees grouping user_ref_id ) tea on tea.event_ref_id = t.webinar_ref_id -- left bring together tbl_event_registrants ter on ter.event_ref_id = t.webinar_ref_id -- left bring together tbl_event_attendees tea on tea.event_ref_id = t.webinar_ref_id inner bring together tbl_user_master tum on tum.user_id = ter.user_ref_id or tum.user_id = tea.user_ref_id t.di_ref_id ='93' grouping tum.user_id

but i'm not sure thats best way go.

here's explain plan

id select_type table type possible_keys key key_len ref rows ------ ----------- --------------------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- ------- -------------------------------- ------ ---------------------------------------------------- 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 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 bring together 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)

in comments said, don't have show sample webinar per user. don't have select webinar table. after want users had part in webinar. select tbl_user_master , create sure user id mentioned in 1 of 2 tables tbl_event_registrants , tbl_event_attendees.

select first_name, last_name tbl_user_master user_id in ( select user_ref_id tbl_event_registrants union select user_ref_id tbl_event_attendees );

mysql can slow on in clauses , union queries however. here same 2 exists clauses:

select first_name, last_name tbl_user_master tum exists ( select * tbl_event_registrants ter ter.user_ref_id = tum.user_id ) or exists ( select * tbl_event_attendees tea tea.user_ref_id = tum.user_id );

if want show users took part in all webinars, you'd have find total number of webinars , compare number of webinars associated user.

select first_name, last_name tbl_user_master tum ( select count(distinct event_ref_id) ( select event_ref_id tbl_event_registrants user_ref_id = tum.user_id union select event_ref_id tbl_event_attendees user_ref_id = tum.user_id ) ) = (select count(*) di_webinar);

edit: here same join:

select tum.first_name, tum.last_name tbl_user_master tum bring together ( select user_ref_id, event_ref_id tbl_event_registrants union select user_ref_id, event_ref_id tbl_event_attendees ) ref on ref.user_ref_id = tum.user_id grouping tum.user_id having count(*) = (select count(*) di_webinar);

mysql sql left-join inner-join