开发者

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

开发者 https://www.devze.com 2023-01-14 18:56 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号