In this lesson, you will learn how to bypass the performance limitations of OR conditions by using UNION ALL to force the database to use your indexes effectively.
The Problem with OR
When you write a query with multiple conditions joined by OR, the database engine often struggles to create an efficient execution plan. For example, if you have a table Orders with an index on Status and another on Category, a query like:
SELECT * FROM Orders
WHERE Status = 'Shipped' OR Category = 'Electronics';
The database often concludes that it cannot use either index effectively. Instead of performing two fast "Index Seeks," it may decide that a "Full Table Scan" is safer, reading every single row to check the conditions. This happens because the engine cannot guarantee that the results won't overlap, leading it to give up on the index entirely.
The UNION ALL Solution
You can manually guide the database to use its indexes by splitting the logic into two separate queries and combining them. By using UNION ALL, you tell the engine to perform two distinct, high-speed index lookups:
SELECT * FROM Orders WHERE Status = 'Shipped'
UNION ALL
SELECT * FROM Orders WHERE Category = 'Electronics'
WHERE Status <> 'Shipped'; -- Optional: prevents duplicates
By separating these, the query optimizer sees a simple request for one index, followed by another. It can perform two "Index Seeks" (or "Index Scans"), which are significantly faster than a full scan of a massive table.
Why this works
When you separate the conditions, you provide the optimizer with a clear, sargable path for each part of the request. You are essentially doing the work of the optimizer by proving that two optimized paths are better than one messy, inclusive path.
Note: Only use UNION ALL if you are certain the result sets don't overlap, or if you don't mind duplicates. If duplicates are a concern, you can use UNION (which performs a distinct sort), though this adds a small cost compared to UNION ALL.
Key Takeaway: When an OR condition forces a slow table scan, split the query into UNION ALL statements to ensure the database utilizes your indexes for each condition individually.