0

I am new to mySQL. I am trying to emulate a full outer join using mySQL. I have two tables each has the following format: table:

id1 | id2 | id3 | id4 | id5 | value

--a-|--b--|--c--|-d--|--e---| 1

--g-|--h--|--i--|--j--|--k--| 1

--w-|--x-|--y--|-z---|--e---| 1

The first five columns are the keys, i.e. no two rows have the same list of keys. I am using the following mySQL script. 

SELECT t1.id1, t1.id2, t1.id3, t1.id4, t1.id5, t1.value, t2.value 
FROM table1 t1 
LEFT JOIN table2 t2 
ON t1.id1=t2.id1 
AND t1.id2=t2.id2 
AND t1.id3=t2.id3 
AND t1.id4=t2.id4 
AND t1.id5=t2.id5  
UNION
SELECT t2.id1, t2.id2, t2.id3, t2.id4, t2.id5, t1.value, t2.value 
FROM table1 t1 
RIGHT JOIN table2 t2 
ON t1.id1=t2.id1 
AND t1.id2=t2.id2 
AND t1.id3=t2.id3 
AND t1.id4=t2.id4 
AND t1.id5=t2.id5 ;

This query is taking a lot of time, I have also increased the connection read timeout to 6000 seconds. So far, I haven't been able to get a query output due to large execution time.

Is there a quicker way to do this, and how can I estimate the amount of time required to execute the query?

Faraz Ahmed
  • 1
  • 1

1 Answers1

0

Try to write with Union all instead of Union .Because Union pulls distinct which takes long time . and use sub query to distinct .

 select distinct t1.id1,*
(SELECT t1.id1, t1.id2, t1.id3, t1.id4, t1.id5, t1.value, t2.value 
FROM table1 t1 
LEFT JOIN table2 t2 
ON t1.id1=t2.id1 
AND t1.id2=t2.id2 
AND t1.id3=t2.id3 
AND t1.id4=t2.id4 
AND t1.id5=t2.id5  
UNION all
SELECT t2.id1, t2.id2, t2.id3, t2.id4, t2.id5, t1.value, t2.value 
FROM table1 t1 
RIGHT JOIN table2 t2 
ON t1.id1=t2.id1 
AND t1.id2=t2.id2 
AND t1.id3=t2.id3 
AND t1.id4=t2.id4 
AND t1.id5=t2.id5 )A