using SQL server 2000
the table has 120,000 rows, and it has columns like as fullName, company, phone, fax, status
the query will be like below, note that 'FirstName', 'LastName' can be replaced to any string, 'myPhone' can be any 10 digit phone and 'myFax' can be any 10 digit fax number.
select fullName, phone,fax
and phone is not null
and (company like 'FirstName%'
or company like 'LastName%'
or fullName like '%FirstName%'
or fullName like '%LastName%'
or phone = 'myPhone'
or acfax = 'myFax')
basically given first name, last name, phone, and fax,
I need to find out any rows that
either phone is the same,
or fax is the same,
or company name starts with first name or starts with last name
or fullName contains either first name or last name
the problem is it is really slow since doing a full table scan, is there any way to improve the performance like setting some index?
thanks in advance