Explain plan will sort the result after join even the column included in index
1
vote
2
answers
163
views
I am using SQL Server 2022 Developer
Trying get all AccessLog that classified to type 1.
SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
LEFT JOIN [AppUser] AS [u] ON [t].[UserId] = [u].[Id]
WHERE EXISTS (SELECT 1
FROM [LogCatalog] AS [c]
WHERE [c].[Type] = 1
AND [c].[Name] = [t].[Type])
ORDER BY [t].[Time] DESC
For 1M record, it will need ~90s to execute on my computer. Most cost is on sort operate.
I already have index on AccessLog.Time DESC, but the plan will sort again still after join.
https://www.brentozar.com/pastetheplan/?id=HyXzc9UUp
I have Index on AccessLog:
1. PK [Id]
2. IX [Time] DESC
3. IX [Time] DESC, [Type] ASC
4. IX [Type] ASC, [Time] DESC
5. IX [Type] ASC
6. IX [UserId] ASC
7. IX [Time] DESC, [UserId] ASC, [Type] ASC
The query filter by [Type] and order by [Time], why the plan can not use the [Time],[Type] index but need to sort again?
Asked by Uni
(11 rep)
Dec 12, 2023, 07:16 AM
Last activity: Jul 10, 2025, 11:06 PM
Last activity: Jul 10, 2025, 11:06 PM