Possible duplicate:
Does "OR" in an INNER JOIN state have a bad idea?
Take a look at this sample table (not real):
CREATE TABLE Orders ( id BIGINT PRIMARY KEY, product VARCHAR(64), author VARCHAR(64), user_id BIGINT ) CREATE TABLE User ( id BIGINT PRIMARY KEY, mail VARCHAR(64) )
Imagine that these tables have indexes for all the columns you would like to look for ...
This is my first request:
SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (o.product in ('aaa','bbb')) OR (o.author in ('user1','user2')) OR (u.mail in (' a@a.com ',' b@b.com '))
First VERY VERY VERY Slow ...
100M orders x 100M users => 10 minutes
This is my second request:
SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (o.product in ('aaa','bbb')) OR (o.author in ('user1','user2')) UNION SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (u.mail in (' a@a.com ',' b@b.com '))
The second is VERY slow ...
100M orders x 100M users => 4 minutes
This is my third request:
SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (o.product in ('aaa','bbb')) UNION SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (o.author in ('user1','user2')) UNION SELECT * FROM Orders o LEFT JOIN user u ON o.user_id = u.id WHERE (u.mail in (' a@a.com ',' b@b.com '))
The third request is really FAST !!!
100M orders x 100M users => 0.1 s
Why can't SQL Server 2005 do this automatically?
UPD : Changed UNION ALL to UNION
UPD2
first and second request: 90% of the plan - "Matching Hash"
Third request: 88% of the plan - "Search for the clustered index."