SQL Server 2005 query with multiple conditions, some indexed, some not I have a table with 50 million rows. It is indexed on zip. A query

Select * 
from table 
where zip = '12345' 


returns 20,000 rows in about 2 seconds

When I add an additional condition it jumps to 15 minutes...

Select * 
from table 
where zip = '12345' 
  and otherField = 'Y'


My expectation is SQL would use the index, identify the 20,000 rows and then scan all for that non indexed condition. Maybe a few seconds at max. Instead it seems to be scanning the whole database table.

I cannot add another index... There are 150+ columns we might test, most have Y/N values.

Is there a better way to write this type of query?

Could I have a setting wrong in the server config?  

Thank you!
Is there a better way to write this type of query?


No, the way you've written it is the "best" way.  Unless, of course, that way doesn't work.  The joy and frustration of using a declarative language is the optimiser.  It is your best friend when it works and worst enemy when it doesn't.

One way to kick the optimiser into doing the right thing is to re-write your query in a semantically identically way such as:

select &lt;whatever&gt;
from table as t
inner join
    (
    Select &lt;primary key columns&gt;
    from table as b
    where b.zip='12345'
    ) as z
    on t.PrimaryKey = z.PrimaryKey  -- repeat if multi-column key
    and t.otherField = 'Y';


Now the optimiser is starting from a different place and may, possibly end up with a different, better, answer. For simple single-table queries I fear it is unlikely to show much change, however.

I would only suggest you try this after you're absolutely sure you've done all you can with indexes.  Re-writing queries in non-obvious ways adds maintenance overhead to the application.  It may also prove counter-productive when you patch or upgrade SQL Server.