SQL LEFT JOIN on 2 keys (join on X OR Y)-CodePudding

SELECT DISTINCT *
FROM institutions
LEFT JOIN units ON Unit_Institution = Institution_Key
LEFT JOIN sites ON Site_Institution = Institution_Key OR Site_Key = Unit_Site

This request has extremely bad performance (despite indexes) because of the OR. Removing one side of it (no matter which one) divides the query cost by thousands. What can I provide the MySQL engine to get solve this performance issue ?

Many thanks.

CodePudding user response：

SELECT DISTINCT <ALIAS>.<FIELD_NAME>,*
FROM INSTITUTIONS INS
LEFT JOIN UNITS UNI  ON UNI.UNIT_INSTITUTION    =   INS.INSTITUTION_KEY
LEFT JOIN SITES SIT1 ON SIT1.SITE_INSTITUTION   =   INS.INSTITUTION_KEY 
LEFT JOIN SITES SIT2 ON SIT2.SITE_KEY           =   (?).UNIT_SITE

try to join the same table two tame, the engine shouldn't need to check the "or" each time, but he will optimize joints with indexes I've tried an example by my own and it's came out that the query with the "or" worked for 52 sec (for 10k records), instead, query with the two "join" took just 6 sec and same number of records

CodePudding user response：

This join is not optimizable to a HASH JOIN, this is the reason it took too long to execute.

My suggestion is splitting the join condition into multiple queries, and then concatenate the results using union method.

i.e:

select distinct * 
from institutions
left join units ON Unit_Institution = Institution_Key
LEFT JOIN sites ON Site_Institution = Institution_Key

union

SELECT distinct *
from institutions 
left join units ON Unit_Institution = Institution_Key
LEFT JOIN sites Site_Key = Unit_Site

CodePudding user response：

You can try this :

SELECT DISTINCT coalesce(sites_si.field, sites_sk.field) AS field -- ...
FROM institutions
LEFT JOIN units ON Unit_Institution = Institution_Key
LEFT JOIN sites_si ON Site_Institution = Institution_Key
LEFT JOIN sites_sk ON Site_Key = Unit_Site

You make two join and use coalesce to put them together. Depending of you data it may be better.