SQL Performance UNION vs OR

by

Either the article you read used a bad example, or you misinterpreted their point.

select username from users where company = 'bbc' or company = 'itv';

This is equivalent to:

select username from users where company IN ('bbc', 'itv');

MySQL can use an index on company for this query just fine. There’s no need to do any UNION.

The more tricky case is where you have an OR condition that involves two different columns.

select username from users where company = 'bbc' or city = 'London';

Suppose there’s an index on company and a separate index on city. Given that MySQL usually uses only one index per table in a given query, which index should it use? If it uses the index on company, it would still have to do a table-scan to find rows where city is London. If it uses the index on city, it would have to do a table-scan for rows where company is bbc.

The UNION solution is for this type of case. 

select username from users where company = 'bbc' 
union
select username from users where city = 'London';

Now each sub-query can use the index for its search, and the results of the subquery are combined by the UNION.

An anonymous user proposed an edit to my answer above, but a moderator rejected the edit. It should have been a comment, not an edit. The claim of the proposed edit was that UNION has to sort the result set to eliminate duplicate rows. This makes the query run slower, and the index optimization is therefore a wash.

My response is that that the indexes help to reduce the result set to a small number of rows before the UNION happens. UNION does in fact eliminate duplicates, but to do that it only has to sort the small result set. There might be cases where the WHERE clauses match a significant portion of the table, and sorting during UNION is as expensive as simply doing the table-scan. But it’s more common for the result set to be reduced by the indexed searches, so the sorting is much less costly than the table-scan.

The difference depends on the data in the table, and the terms being searched. The only way to determine the best solution for a given query is to try both methods in the MySQL query profiler and compare their performance.

More Related Contents:

  1. MYSQL OR vs IN performance
  2. MySQL – How to unpivot columns to rows?
  3. Combining UNION and LIMIT operations in MySQL query
  4. Using union and order by clause in mysql
  5. UNION query with codeigniter’s active record pattern
  6. MySQL “IN” operator performance on (large?) number of values
  7. What is better in MYSQL count(*) or count(1)?
  8. MySQL: Inner join vs Where [duplicate]
  9. Does Order of Fields of Multi-Column Index in MySQL Matter
  10. MySQL DELETE FROM with subquery as condition
  11. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  12. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  13. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  14. Simple Random Samples from a Sql database
  15. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  16. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  17. Determine Rank based on Multiple Columns in MySQL
  18. Checking multiple columns for one value
  19. How to get the count of each distinct value in a column? [duplicate]
  20. How to enable INNODB in mysql
  21. Bash script to insert values in MySQL
  22. MySQL GROUP BY DateTime +/- 3 seconds
  23. How do I lag columns in MySQL?
  24. How to get multiple records against one record based on relation?
  25. SQL query to check if a name begins and ends with a vowel
  26. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  27. How to use a user variables in MySQL LIKE clause?
  28. sql group by versus distinct
  29. Updating table in trigger after update on the same table
  30. Deleting a row based on the max value

Leave a Comment