Here's the query:

SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER JOIN  dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7

Location Indexes:

PK_Locations:

LocationId

IX_Locations_CountryId_Type:

CountryId, Type

LocationCache Indexes:

PK_LocationCache:

LocationId

IX_LocationCache_LocationId_SearchQuery_SearchRank:

LocationId, SearchQuery, SearchRank

Execution Plan:

enter image description here

So it's doing a Index Seek on Locations, using the covering index, cool.

But why it is doing a Index Scan on the LocationCache covering index?

That covering index has LocationId, SearchQuery, SearchRank in the index (not as "Included columns").

Hover on the index scan:

enter image description here

This query needs to go in an indexed view served by a SQL Server FTS catalogue, consumed by an autocomplete plugin, so it needs to be 100% optimized.

At the moment that above query is taking 3 seconds. It should be < 0.

Any ideas?

Edit
Report