sql server - Make use of index when JOIN'ing against multiple columns -

simplified, have 2 tables, contacts , donotcall

create table contacts (     id int primary key,     phone1 varchar(20) null,     phone2 varchar(20) null,     phone3 varchar(20) null,     phone4 varchar(20) null ); create table donotcall (     list_id int not null,     phone varchar(20) not null ); create nonclustered index ix_donotcall_list_phone on donotcall (     list_id asc,     phone asc ); 

i see contacts matches phone number in specific list of donotcall phone. faster lookup, have indexed donotcall on list_id , phone.

when make following join takes long time (eg. 9 seconds):

select distinct c.id contacts c join donotcall d     on d.list_id = 1     , d.phone in (c.phone1, c.phone2, c.phone3, c.phone4)   

execution plan on pastebin

while if left join on each phone field seperately runs lot faster (eg. 1.5 seconds):

select c.id contacts c left join donotcall d1     on d1.list_id = 1     , d1.phone = c.phone1 left join donotcall d2     on d2.list_id = 1     , d2.phone = c.phone2 left join donotcall d3     on d3.list_id = 1     , d3.phone = c.phone3 left join donotcall d4     on d4.list_id = 1     , d4.phone = c.phone4     d1.phone not null     or d2.phone not null     or d3.phone not null     or d4.phone not null 

execution plan on pastebin

my assumption first snippet runs because doesn't utilize index on donotcall.
so, how join towards multiple columns , still have use index?

sql server might think resolving in (c.phone1, c.phone2, c.phone3, c.phone4) using index expensive.

you can test if index faster hint:

select c.* contacts c join donotcall d (index(ix_donotcall_list_phone))     on d.list_id = 1     , d.phone in (c.phone1, c.phone2, c.phone3, c.phone4) 

from query plans posted, shows first plan estimated produce 40k rows, returns 21 rows. second plan estimates 1 row (and of course returns 21 too.)

are statistics date? out-of-date statistics can explain query analyzer making bad choices. statistics should updated automatically or in weekly job. check age of statistics with:

select  object_name(ind.object_id) tablename ,       ind.name indexname ,       stats_date(ind.object_id, ind.index_id) statisticsdate    sys.indexes ind order          stats_date(ind.object_id, ind.index_id) desc 

you can update them manually with:

exec sp_updatestats;