Why Adding an OR Clause Makes a MySQL Query 40× Faster – Materialization vs Semi‑Join

Background

An original MySQL performance case shared by DBA+ community expert Li Haixiang (Oracle MySQL global development team) demonstrates a striking difference between two seemingly identical queries on MySQL 5.6/5.7.

Test Setup

A simple table is created and populated via a stored procedure. The two queries (Q1 and Q2) are then executed.

Query Q1

Query Q2

Q2 adds an OR condition that matches no rows in the data.

Observed Performance

Both queries are rewritten to use COUNT() instead of returning full result sets. Execution timing shows that Q1 is roughly 40 times slower than Q2.

Root‑Cause Analysis

1. Execution Plan

Q1’s plan shows a MATERIALIZED subquery, meaning MySQL materializes the subquery into a temporary table.

2. I/O and Index Reads

Q2 exhibits a much higher Handler_read_key count (20002 vs 2), indicating extensive index usage.

MySQL documentation defines Handler_read_key as the number of row reads based on a key; a high value suggests good indexing.

3. Semi‑Join Optimization

Q1’s materialized subquery is further optimized by a semi‑join (subquery “pulled up”). Q2, while also using a temporary table, does not receive this semi‑join optimization.

MySQL automatically creates an index on the temporary table (e.g., auto_key ) and performs a primary_index_lookup , which explains Q2’s faster execution despite using a temporary table.

Further Experiments

Disabling the semi‑join operation dramatically improves Q1’s speed, yielding a >40× performance gain.

Conclusion

1. Q1’s combination of materialization and semi‑join optimization makes it significantly slower than Q2, which avoids the semi‑join.

2. The case reveals that MySQL’s semi‑join optimizer may not always yield performance benefits, and that materialized subqueries can introduce costly temporary tables.