Using partial index in a query.

Hello,

I created a non-clustered index using 2 columns in a table. When i query the table using one column of that index on the where clause, the time cost is equal as not using the index at all. Is this possible?

----------
The table has over 1 million records:
----------

id integer PK
id_machine integer
db_timestamp date
machine_timestamp date
machine_status integer
machine_timeout integer
machine_event varchar2(100)

----------
The index is on id_machine and machine_timestamp:
----------

CREATE
INDEX [idx_events_machine_timestamp] ON [master].[events] ([id_machine ], [machine_timestamp] desc )
WITH
DROP_EXISTING
ON [INDX]

----------
The query is the following. I do other queries but they use the full index and there's no problem with them. Can i avoid creating another index for this specific query?
----------

select db_timestamp
from master.events
with (INDEX=idx_events_machine_timestamp)
where machine_timeout=1
and id_machine =520
order by db_timestamp desc


Thanks.

It happened, so the answer is clear, it is possible.

Using non-clustered index less rows are accessed because of the condition id_machine = 520 but additional bookmark lookup has to be done for each entry that satisfies the condition because the condition machine_timeout = 1 has to be checket and the column machine_timeout is not part for non-clustered index you defined. Which plan is better depends on percent of rows accessed by the condition id_machine = 520 and how does alternative (not forced) execution plan look like.

With the forced index, it does a "index seek" on "idx_events_machine_timestamp" with "bookmark lookup".

Without the forced index it uses the "clustered index scan" on the primary key column "id" which is a auto-number sequence.

I'm new to SQL SERVER (too obvious, isn't it!?!), but i've worked with Oracle and i notice query performance improvements when i use indexes only partially.

Do you have a suggestion on how to improve the query?
Or is creating another non-clustered index the only solution?

Thanks.

It may help, but it doesn't have to. It depends on data distribution. You can try to add one (machine_timeout) or two (machine_timeout and db_timestamp) as the last (two) column(s) in the index and see how much improvements you get. Covering index will be much faster for sure, but you shouldn't make decision about index design based on just one query.