Why UNION Queries Outperform OR Conditions in MySQL: A Deep Dive

In many SQL‑optimization guides it is suggested to replace an OR condition with a UNION query.

Example

Using OR: SELECT * FROM a, b WHERE a.p = b.q OR a.x = b.y; Using UNION (producing the same result but faster):

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;

Although UNION executes two separate SELECT statements, the OR version performs a single scan and can be slower. Why?

Test case

We query the users table, which has an index on user_id, looking for rows with user_id='IjPEBWuEQZ' or user_id='FwYEz8Bzp'.

OR query:

SELECT * FROM users WHERE user_id='IjPEBWuEQZ' OR user_id='FwYEz8Bzp';

UNION query:

SELECT * FROM users WHERE user_id='IjPEBWuEQZ'
UNION
SELECT * FROM users WHERE user_id='FwYEz8Bzp';

Running EXPLAIN on both statements shows the following differences:

The key differences are the type and ref columns in the EXPLAIN output. UNION yields type =ref and ref =const, while OR yields type =range and ref =null. Since ref =const is a direct constant lookup, UNION is clearly faster.

In short, UNION uses a precise index lookup for each constant value, whereas OR forces the optimizer to compare multiple values, resulting in a less efficient range scan.