LEFT OUTER JOIN with an OR condition <- kills db server

Question

In a nutshell..

SELECT * 
FROM tbl1 
LEFT OUTER JOIN tbl2 ON tbl1.ID = tbl2.externalRefID 
WHERE tbl1.varref = '12345' OR tbl2.varref = '12345';

With any volume of data in these tables this query takes forever. Yet I need the second table outer joined as it won't often match on the externalRefID. Indexes created for all columns that are referenced but makes no difference as I need an index across multiple tables (is that possible?)

this must be obvious but i can't see the wood for the trees...

Please post DDL for both tables. What mysql engine are you using InnoDB, MyISAM? — peterm, Apr 03 '13 at 11:54

score 0 · Accepted Answer · answered Apr 03 '13 at 12:05

Have you considered refactoring the query into a UNION query?

SELECT *
  FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.externalRefId
 WHERE tbl2.varref = '12345'
UNION
SELECT *
  FROM tbl1 OUTER JOIN tbl2 ON tbl1.id = tbl2.externalRefId
 WHERE tab.1.varref = '12345'

You might be able to use UNION ALL on this too.

LEFT OUTER JOIN with an OR condition <- kills db server

1 Answers1