Re: inner join on key does full table scan anyway

[Chris Schneider]

You could use LEFT or RIGHT join on this, that is, if don't wast to join ALL of the rows in each table regardless of NULL...

test> EXPLAIN SELECT * FROM a left JOIN b ON b.z = a.x OR b.z = a.y;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | a | index | NULL | y | 5 | NULL | 2 | Using index |
| 1 | SIMPLE | b | index | PRIMARY | PRIMARY | 4 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

otherwise two queries would be better.

Careful with UNION because it will truncate duplicates.