Possible Duplicate:
Is having an 'OR' in an INNER JOIN condition a bad idea?
Look at this example of tables (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, this tables have indexes for all columns you would like to search...
This is my first query:
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 is VERY-VERY-VERY slow...
100M Orders x 100M Users => 10 mins
This is my second query:
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'))
Second is VERY slow...
100M Orders x 100M Users => 4 mins
This is my third query:
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'))
Third query is really FAST!!!
100M Orders x 100M Users => 0.1 sec
Why SQL Server 2005 can't do this automatically?
UPD: Changed UNION ALL to UNION
UPD2:
first and second query: 90% of plan is "Matching Hash"
third query: 88% of plan is "Clustered index search".
