Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be.
I have a query inside an indexed view, like this:
SELECT [a].[ListingId],
-- lots of other columns from [a], [b], [c], [d] etc
FROM [dbo].[Listings][a]
INNER JOIN [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId]
INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId]
INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId]
WHERE [a].[IsVisible] = 1
AND [a].[IsLive] = 1
AND [a].[AgencyCompanyId] IS NOT NULL
Note the 3 predicates.
I created a covering index for the predicates, and included the ListingId
column, hoping the query optimizer would use the index then do a KEY LOOKUP
to fetch the other columns from that table, then do a clustered index seek to get the other table data.
Here's the index definition:
CREATE NONCLUSTERED INDEX [IX_Listings_ForListingSearchView] ON [dbo].[Listings]
(
[IsVisible] ASC,
[IsLive] ASC,
[AgencyCompanyId] ASC
)
INCLUDE ( [ListingId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Quite the contrary, here's the estimated execution plan:
My questions:
- Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?
- I noticed if i change the query to just fetch
a.ListingId
and not the rest of the columns froma
, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then? - Why is the query optimizer doing an clustered index scan and not a seek?
- This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?