CODEWITHSUNDEEP
sqlindexing
user2124871
user2124871

Reputation: 813

Why is my query plan generating an index scan?

I have a relatively straight forward query:

    SELECT o.id
    FROM dbo.table1 o
    LEFT JOIN dbo.table2 n (NOLOCK)
        ON o.first = n.second and n.first = @Var1
    WHERE 
        (o.fourth in (@Var4, @Var5) or o.fifth = @Var6) AND
        (o.first = @Var1 or n.first = @Var1) AND
        (
            o.second = @Var2 OR
            o.second like (@Var2 + '[^a-zA-Z]%') OR
            o.third like (@Var3 + '[^a-zA-Z]%')
        )

But whenever I check out the query plan, I am doing an index scan.

The non-clustered index I have is (on table1):

First ASC,
Fourth ASC,
Fifth ASC,
Second ASC,
Third ASC

Include: id

Why a scan? Doesn't my index cover it? I also have the correct indexes for table2, so I am not worried there.

Thanks for any help

Upvotes: 3

Views: 61

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

The only places where you are using the First column from table 1 are in places where a failure to have a match is not necessarily a failure for the whole query

  • inside the ON of a LEFT JOIN
  • On only one side of an OR operator

Therefore, it cannot start to benefit from using an index with First as the first column, since literally any value in that column might be a valid part of the final result.

Upvotes: 1

Related Questions