During my application I've two queries which is often used. The Where clauses of those queries would be the following:
WHERE FieldA = @P1 AND (FieldB = @P2 OR FieldC = @P2)
WHERE FieldA = @P1 AND FieldB = @P2
P2 are parameters joined within the UI or originating from exterior datasources.
- FieldA is definitely an
intand highly non-unique, means: only two, three, four different values inside a table with say 20000 rows
- FieldB is really a
varchar(20)and it is "almost" unique, you will see only very couple of rows where FieldB might have a similar value
- FieldC is really a
varchar(15)as well as highly distinct, but less than FieldB
- FieldA and FieldB together are unique (but don't form my primary key, the industry simple auto-incrementing identity column having a clustered index)
I am wondering now what's the easiest method to define a catalog to accelerate particularly both of these queries. Shall I define one index with...
FieldB (or better FieldC here?) FieldC (or better FieldB here?) FieldA
... or better two indices:
Or exist even other and options? What's the easiest way and why?
Appreciate suggestions ahead of time!
Just like a data with other visitors: Here was another answer that has been erased now. Really the solution appeared very helpful in my experience. The recommendations ended up being to create two indices (based on my second item above) and also to reformulate the very first query using a
UNION of two choose claims (one with
WHERE FieldA = @P1 AND FieldB = @P2 and something with
WHERE FieldA = @P1 AND FieldC = @P2) rather than
OR to profit from both indices (which wouldn't be using the OR-operator).
The statement by using Or even the indexes aren't used which a UNION is more suitable appears to become wrong - a minimum of based on my very own tests (see my very own answer below).
Stretching Remus' (edit: now erased) answer...
- if @p2 is varchar(15) then you definitely can't compare against FieldB dependably, it's varchar(20)
- if @p2 is varchar(20) then FieldC is going to be transformed into varchar(20) and never make use of an index (or otherwise scan it)
- if @p1 has only 2, 3, 4 values then why don't you tinyint and lower table/index size?
I wouldn't make use of indexes before you resolve this datatype priority problem: this really is on the top from the OR clause problem.
Finally, a column is exclusive or non-unique: there's no between. Statistics help here with selectivity, but it is irrelevant.
I'd turn back indexes from Remus' response to be
FieldB, FieldA (and different) and
FieldC, FieldA due to FieldA's selectivity
Edit, after comments: you cannot compare using @p2 against using constant strings.