Not Using Index...

[dtfhome]

I have a database on SQL Server without about 2 million company records in the company table. The query I am having a problem with queries the company table and a joined lookup table of only about 8 records (holding status descriptions for the numberic status field)

Most of the fields contain your basic address data (name, address, city, state, zip) and a few additional numeric fields contain descriptive information such as a status code or a source code.

There are two types of users that will query the data through a cold fusion application. One type to query for a list of company names based on certain criteria, the other for viewing different views of the results from activity. Because we are quering a rather large database of companies, we have set-up a about 8 indexes in the Enterprise Manager to speed things up. Rather new with indexing and all of the indexes are for a single field (company index on the company field, zip index on the zip field).

These indexes had worked great and the following query would return results from the 2 million records very fast and consistent...

select id,company,callstatus,zip,state,phone,modby,modon
from companies
zip IN (#quotedvaluelist(form.zip)#)
and callstatus IN (#valuelist(form.callstatus)#)

Again, the above query works great because I have an index set for zip and callstatus. The problem comes if I try to add the following on the end of the query...

and source = 1

There is an index set for source but when we run the page, it does not use the index and the page loads very slow before returning the results. If I take out the above line, the query runs fast again.

Source is numberic.
The index was set up exactly as the others.
The value is either 1 or NULL in the table for each record (about 100,000 = 1, all the rest = NULL).
It was the last field added to the table.
It is the last index added in Enterprise Manager.

What is going on? Why is the index not working?

Any suggestions or comments are greatly apprecitated.

Thanks.

 

[MeanGreen]

The last index is not working because SQL Server considers the index insignificant relative to the amount of rows that you have. Basically, with only 100,000 of 2 million rows, MS SQL is choosing a table scan. Try creating a composite Index with zip, callstatus, and source (in that order) and see if the query will use the index. If it does not, then maybe try to use an INDEX hint to force it to use your created index and see if that helps.
Example:
select id,company,callstatus,zip,state,phone,modby,modon
from companies (INDEX = My_New_Index)
zip IN (#quotedvaluelist(form.zip)#)
and callstatus IN (#valuelist(form.callstatus)#)
and source = 1

Hope this helps.