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?