sqlindexingsybasesqlanywhere

Why the indexes not speeding up the query?

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?

Solution

  • 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'