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.
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
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?