Friday, March 30, 2012

How do I make it use my index?

Hello!

I have two tables

users and pictures.

table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid

when I do this query:

"select userid from pictures where userid=123"

then It will do a clustered index seek

But If I do any of those:

"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"

It will do a clustered index scan.

How can I force it to seek my index instead of scan?

Thanks!do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

option(loop join)

at the end of the query. this should solve your problem

No comments:

Post a Comment