OR vs IN vs BETWEEN for multi-value WHERE

When you need to match an indexed column against several values, MySQL’s behaviour for the four obvious shapes is dramatically different. Illustrative timings (one run, an indexed primary key, three values):

Query shape	Time
`WHERE id = 50 OR id = 51 OR id = 52`	3.700 s
`WHERE id IN (50, 51, 52)`	0.037 s
`WHERE id >= 50 AND id <= 52`	0.005 s
`WHERE id BETWEEN 50 AND 52`	0.005 s

A 100× speedup from OR to IN, and another ~7× from IN to BETWEEN. The reasons are mechanical, not stylistic.

Why `IN` is faster than chained `OR`

In some databases IN(...) is just sugar for chained ORs. In MySQL it isn’t. MySQL sorts the values in the IN list once, then performs a binary search to test membership — O(log n). Chained ORs are evaluated as a sequence of independent equality checks — O(n). For three values the difference is small, for thirty it’s large, for three hundred it’s the difference between “fast” and “stop the query.”

The optimizer also has more freedom with IN. It can convert IN against an indexed column into an index range scan over a sorted list of point lookups. With OR, that conversion is conditional — the optimizer may use the index merge / union strategy, but only when each branch references the same indexed column and the planner decides the union is cheaper than the alternative. Complex OR chains lose that and fall back to a full scan.

Why `BETWEEN` beats `IN` for contiguous ranges

BETWEEN a AND b and >= a AND <= b are identical to the optimizer — both compile to a single index range scan. The engine seeks to the first matching key, walks the leaf pages in order, and stops at the upper bound. One descent into the B-tree, one sequential walk.

IN (50, 51, 52) is three separate ref / range lookups: descend to 50, descend to 51, descend to 52. Each descent costs O(log n) index navigation. For three values that’s three descents; for a tight contiguous list it’s strictly more work than a single range walk.

The advantage flips back to IN when the values are non-contiguous — IN (50, 200, 9000) can’t be expressed as a range, so BETWEEN would either be wrong or include rows you don’t want.

Contiguous values

-- BETWEEN — one range scan
SELECT * FROM tbl WHERE id BETWEEN 50 AND 52;

Non-contiguous values

-- IN — three point lookups (still very fast)
SELECT * FROM tbl WHERE id IN (50, 200, 9000);

The decision

Axis	IN (list)	BETWEEN a AND b
When the values are contiguous	Multiple point lookups	Single range scan — fastest
When the values are non-contiguous	Correct — one lookup per value, O(log n) each	Wrong — would include non-matching rows
Index usage	Sorted list + binary search	Index range scan
Readability	Explicit set membership	Explicit range

When `OR` is still the right shape

OR is the only correct choice when the predicates reference different columns:

WHERE status = 'paid' OR refunded_at IS NOT NULL;

Here IN can’t help — the operands aren’t the same column. Whether the optimizer can use indexes from both sides depends on the index_merge strategy: it may scan each predicate’s index separately and union the row IDs. Check EXPLAIN — Using union(idx_status,idx_refunded_at) in Extra means index_merge fired; absence usually means a full table scan.

The rule

Same column, contiguous values → BETWEEN (or >=/<=).
Same column, non-contiguous values → IN.
Same column, a few values written as OR → rewrite as IN. No exceptions.
Different columns → OR is correct, but verify the plan with 0007 EXPLAIN · MySQL . Look for index_merge; if it’s missing, consider UNION ALL.

What to remember

IN and OR are not synonyms in MySQL. IN is a sorted-list binary search; OR is a linear chain. BETWEEN is a range scan and beats IN when the values form a contiguous range. Confirm any of the above with 0007 EXPLAIN · MySQL — the difference between range, ref, and ALL in the type column is exactly the difference between these query shapes. See also 0005 SARGable predicates · MySQL for the broader pattern of keeping the WHERE clause index-friendly.