I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:
SELECT *
FROM Users u
LEFT JOIN Comments c
ON u.Id = c.UserId OR
u.Id = c.PostId
WHERE u.DisplayName = 'alex'
The only index on the Users
table is a clustered index on ID.
The Comments
table has the following Non-Clustered indexes as well as Clustered Index on ID:
CREATE INDEX IX_UserID ON Comments
(
UserID,
PostID
)
CREATE INDEX IX_PostID ON Comments
(
PostID,
UserID
)
The estimated plan for the query is here:
I can see the first thing the optimizer will do is perform a CI scan on the users table to filter only those users where DisplayName = Alex
, effectively doing this:
SELECT *
FROM Users u
WHERE u.DisplayName = 'alex'
ORDER BY Id
and retreiving results as such:
Then it will scan the comments CI and for every row, look to see if the row satisfies the predicate
u.Id = c.UserId OR u.Id = c.PostId
Despite the two indexes, this CI scan is performed.
Wouldn't it be more efficient if the optimizer did a separate seek on each of the indexes in the Comments table above and join them together?
If I visualise what that would look like, in the screenshot above we can see the first result of the Users CI scan is ID 420
I can visualize what the IX_UserID
Index looks like using
SELECT UserID,
PostID
FROM Comments
ORDER BY UserID,
PostID
so if I seek to the rows for user ID 420 as an index seek would:
for every row where UserID = 420
, I can look if u.Id = c.UserId OR u.Id = c.PostId
of, course they all match the u.Id = c.UserId
part of our predicate,
So for the second part of our index seek, we can seek through our index IX_PostID
which can be visualised as follows:
SELECT PostID,
UserID
FROM Comments
ORDER BY PostID,
UserID
If I seek to Post ID 420 I can see nothing is there:
So we then go back to the results of the CI scan, move to the next row (userId 447) and repeat the process.
The behaviour I have described above is possible using in a WHERE
clause:
SELECT UserID,
PostID
FROM Comments
WHERE UserID = 420 OR PostID = 420
ORDER BY UserID,
PostID
My question therefore is, why isn't an OR
condition in a JOIN
clause able to perform an index seek on appropriate indexes?