Geek Answers Handbook

What indexes should be created to optimize a SQL query with several OR conditions

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 indexes should I add to improve query performance? I also need to implement swap, so this request will be more complex.

I assume that it is necessary to create four indexes: (A, B, F), (A, C, F), (A, D, F) (A, E, F), but I'm not sure and cannot verify this, because I don’t have enough data yet.

Does anyone have any experience to share? Thanks.

+6
sql sql-server
source share
2 answers

Indexes, as a rule, do not help you with similar logic OR. Not knowing how much data you get or the complexity of the objects, I can only speak subjectively, but it is often faster to use join queries to sort the data.

 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
+4
source share

The coverage index should be used.

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

remember, select *, which is not easily indexed. instead, an index for what you need.

+1
source share

More articles:


All Articles