SQL Performance: When Is UNION Better Than OR?

SQL performance: The controversy between UNION and OR

Simply thinking that replacing OR statements with UNIONs will improve SQL performance is an oversimplification. However, understanding the nuances of how they are used can lead to significant performance improvements.

Understand the differences

OR executes a single query and retrieves rows that satisfy either condition. UNION, on the other hand, executes two separate queries and combines their results. This distinction becomes critical when the OR involves multiple columns.

Consider the following hypothetical query:

<code class="language-sql">select username from users where company = 'bbc' or city = 'London';</code>

If both company and city columns have indexes, MySQL needs to choose an index. This may result in a full table scan of another column.

UNION as the solution

UNION solves this problem by detaching the query:

<code class="language-sql">select username from users where company = 'bbc'
union
select username from users where city = 'London';</code>

Each subquery effectively utilizes its respective index, and the final result is the union of the two sets.

Notes on sorting

UNION does require sorting to eliminate duplicates in the result set. However, since UNION focuses on a smaller set of rows, the overhead is usually negligible. In most cases where a WHERE clause affects a table, the cost of a full table scan can exceed the cost of a UNION sort.

Conclusion

UNION is not absolutely better than OR. However, it can optimize performance when OR involves predicates on columns with conflicting indexes. Understanding these nuances is critical to writing efficient SQL queries.

The above is the detailed content of SQL Performance: When Is UNION Better Than OR?. For more information, please follow other related articles on the PHP Chinese website!