Rj. Rj. - 7 months ago 7
SQL Question

Select query too slow even though I'm using an index

I'm not sure if I'm doing something wrong here but I have a query running on a table with millions of rows.

The query is something like this:

select *
from dbo.table with (index (index_1), nolock)
where col1 = 15464
and col2 not in ('X', 'U')
and col3 is null
and col4 = 'E'


Index looks like this:

CREATE NONCLUSTERED INDEX [index_1] ON [dbo].[table] ([col1], [col2], [col3], [col4]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO


This select still takes over a minute to run. What am I missing?

Answer

For this query:

select * 
from table 
where col1 = 15464 and
      col2 not in ('X', 'U') and
      col3 is null and
      col4 = 'E';

The best index is table(col1, col4, col3, col2). The query should use the index automatically, without a hint.

When choosing an index based on a where clause, you should put the equality conditions in first -- followed by one column with an inequality. For the purposes of indexing, in and not in are inequality conditions in general.

Also, if you mix data types, then sometimes indexes are not used. So, this assumes that col1 is numeric.