Question 112980 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL LEFT JOIN on 2 keys (join on X OR Y)

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.

Tags:

3

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search