MySql JOIN query with OR clause very slow

问题:

I have the following query:

SELECT a.id, b.from, b.to FROM a INNER JOIN b ON a.id = b.from OR a.id = b.to;

which is extremely slow.

If I remove the OR clause and run each query separately then the both queries execute under 1 second.

SELECT a.id, b.from, b.to FROM a INNER JOIN b ON a.id = b.from;
SELECT a.id, b.from, b.to FROM a INNER JOIN b ON a.id = b.to; 

How can I speed up the original query (set up indexes) or redesign the query itself?

回答1:

What about using union?

SELECT a.id, b.from, b.to FROM a INNER JOIN b ON a.id=b.from
UNION
SELECT a.id, b.from, b.to FROM a INNER JOIN b ON a.id=b.to

回答2:

How about the following instead. Just join to b twice:

SELECT a.id, b.from, b2.to 
FROM a 
INNER JOIN b ON a.id = b.from
INNER JOIN b b2 ON a.id = b2.to;

You may have to use LEFT JOIN instead if you don't always have a record for both cases.