CODEWITHSUNDEEP
mysqlsqldatabasejoinhana
EpsilonTal
EpsilonTal

Reputation: 457

How to improve performance of a SQL JOIN with an OR condition

Complicated JOIN query as:

SELECT 
  FIRST.NAME,
  SECOND.FIRST_NAME,
  SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
LEFT JOIN SECOND_TABLE SECOND 
  ON (SECOND.FIRST_NAME = FIRST.NAME OR SECOND.LAST_NAME = FIRST.NAME)

Will result with bad performance.

How to get better performance?

Upvotes: 0

Views: 732

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

You may be better off with exists:

SELECT FIRST.ID, FIRST.NAME
FROM FIRST_TABLE FIRST 
WHERE EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE SECOND.FIRST_NAME = FIRST.NAME) OR
      EXISTS (SELECT 1 FROM SECOND_TABLE SECOND WHERE  SECOND.LAST_NAME = FIRST.NAME);

Then for performance, you want indexes on SECOND_TABLE(LAST_NAME) AND SECOND_TABLE(FIRST_NAME).

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

You could try this, see what you get. It would depend a lot on the indexes on FIRST_NAME and LAST_NAME, but it avoids the OR.

SELECT 
  FIRST.NAME,
  SECOND.FIRST_NAME,
  SECOND.LAST_NAME
FROM FIRST_TABLE FIRST
  LEFT JOIN (
     SELECT S1.FIRST_NAME as TEST_NAME,
            S1.FIRST_NAME,
            S1.LAST_NAME       
     FROM   SECOND_TABLE S1
     WHERE  S1.FIRST_NAME <> S1.LAST_NAME
     UNION ALL
     SELECT S2.LAST_NAME as TEST_NAME,
            S2.FIRST_NAME,
            S2.LAST_NAME       
     FROM   SECOND_TABLE S2
   ) AS SECOND
  ON FIRST.NAME = SECOND.TEST_NAME

Upvotes: 0

Related Questions