Non Clustered Index Question

[wurzel]

I was under the impression that a non clustered index hould be created where the query results will be highly selective.

The execution plan for a badly performing query is prompting me to put a nc index on a status field; one that can only have 1 of 5 values. In fact, the clause statusid <> 11 brings back approx 25% of a table containing 300,000 rows.

Suggested index also includes all the other fields in the table but only statusid is in the index.

The optimiser uses the index & the query performs much better.
I just don't get it as it sees to go against all the rules.

Can anyone see what's going on?

This is the query:

SELECT wi.*,wia.*
FROM WorkItem wi
LEFT OUTER JOIN WorkItemAction wia ON wia.WorkItemId = wi.WorkItemId
WHERE wi.StatusId NOT IN (11, 15, 99)
AND wia.statusid <> 11
AND (wi.OwnerTeamMember_TeamMemberId = @teammemberid OR wia.AllocatedTeamMember_TeamMemberId = @teammemberid )
ORDER BY wia.nextactiondate, wi.nextretrydate, wi.priority, wi.currentweighting desc

[Spacecadet]

even on columns with a low cardinality, an index will stop the SQL engine having to perform a table scan

There is also the issue of statistics
you may have only 4 different values in a column but if 90% are one value, 7% another, 2% the third with the fourth value getting 1%, then the index with statistics will be able to find that 1% a lot faster than without.