user1723974 user1723974 - 3 months ago 15
MySQL Question

MySQL LEFT JOIN on either of two columns

I want to join two tables on one of either two columns. I don't know which one will be a match. I want ALL records from feed_REIN and additional data when a matching row is found from listings table. I figured a LEFT JOIN would work.

When I just have the one condition (i.e. RETS.list_number = listings.CVMLS) it runs OK. As soon as I add the additional OR condition in the LEFT JOIN it takes forever to finish the query.

feed_RETS has about 125k records and listings has about 12k records.

What am I doing wrong?

SELECT

COUNT(*)

FROM feed_RETS AS RETS LEFT JOIN listings ON listings.statusID IN (1,2,3) AND (RETS.list_number = listings.CVMLS OR RETS.list_number = listings.REIN)

WHERE RETS.public_status NOT LIKE '%Sold%'

Answer

Look at explain plan to better understand the query with an OR clause. It's likely that the only possible join algorithm for this query is nested loop join, which is very inefficient for your tables. You can rewrite the query to something like:

SELECT 
COUNT(*)
FROM(
(SELCT * FROM feed_RETS AS RETS LEFT JOIN listings ON statusID IN (1,2,3) AND RETS.list_number = listings.CVMLS WHERE public_status NOT LIKE '%Sold%')
UNION
(SELCT * FROM feed_RETS AS RETS LEFT JOIN listings ON statusID IN (1,2,3) AND RETS.list_number = listings.REIN WHERE public_status NOT LIKE '%Sold%'))T