Sample Header Ad - 728x90

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