SQL - OR vs UNION

This post references Leetcode SQL Question 595. Big Countries.

Question

Write a query to report the name, population, and area of the big countries. A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

SQL Schema

If you have your own database set up, insert the following:

Create table If Not Exists World (name varchar(255), continent varchar(255), area int, population int, gdp bigint)
Truncate table World
insert into World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000')
insert into World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000')
insert into World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000')
insert into World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000')
insert into World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000')

Using OR

SELECT name, population, area FROM World
WHERE area >= 3000000 OR population >= 25000000

Using UNION

SELECT name, population, area FROM World
WHERE area >= 3000000 OR population >= 25000000

Why is UNION faster than OR?

In this context, we are scanning two different colums - area and population and MySQL usually uses one index per table to scan the table.

Using OR will result in a rescan to find rows that fit the 2nd index.

Using UNION, we are separaing the scan into two separate query, each with one index to scan, before combing the result together.

So, in this simple query UNION is faster because it is faster to scan separately with one index and combining the rows compared to scanning two indexes in one query.

But UNION is not always faster than OR. The speed at which union operation executes depend on the size of the sets being combined.