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 t开发者_StackOverflow中文版o share? Thanks.
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
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.
精彩评论