What indices should be created to optimize sql query with multiple OR conditions

Question

I have a query like this:

SELECT * 
FROM T
WHERE A = @A AND (B=@B OR C=@C OR D=@D OR @E=E)
ORDER BY F

What indices should I add to improve query performance? Also I'll need to implement paging so this query will be more complex.

My guess is that four indices should be created: (A, B, F), (A, C, F), (A, D, F) (A, E, F), but I'm not sure and can't really test it as I don't have enough data yet.

Does anyone have some experience to share? Thanks.

Answer 1

Indices generally don't help you with this sort of OR logic Not knowing how much data you are getting or the complexity of the objects I can only speak subjectively but its often quicker to use union queries to sort the data down.

SELECT * from T
WHERE a= @a  and B= @b
UNION  
SELECT * from T
WHERE a= @a  and c= @c
UNION
SELECT * from T
WHERE a= @a  and d= @d
union
SELECT * from T
WHERE a= @a  and e= @e

Answer 2

A covering index should do.

created nonclustered index ([IDX_Cover])
on dbo.Table (columns to select)
include (columns in where clause)

remember, select * is not easily indexable. instead, index for what you need.