Why the indexes not speeding up the query?

Question

I have the following query:

SELECT TOP 1000 * 
FROM MyTable 
WHERE Status = 'N' AND Type is not null 
ORDER BY mytable.id

MyTable has 130 million rows.

I also created these indexes:

CREATE INDEX "MyTableIndex_1" ON MyTable (Status);
CREATE INDEX "MyTableIndex_2" ON MyTable (Type);

The ID column was already a clustered index.

Somehow the query is still very slow. What am I missing?

Answer 1

try a multi column index

CREATE INDEX "MyTableIndex_StatusType" ON MyTable (Status, Type);

if that doesn't work then do some research on 'Covering Indexes'

Answer 2

Type is not null , the index on this column might not help.

BTW, can you try update index statistics before the query?