Sargable

The ideal SQL search condition is in the form of .  The left side is a column name and the right side is a easy to lookup value.  IBM called these kinds of condition "sargable predicates".  SARG is short for "Searchable ARGument".  Microsoft and Sybase later redefined the term as "can be looked up via index".

Each component of a search condition has a point count. The higher the count, the faster the component (i.e. imvolves fewer row or easier to compare).  For operator, the relative sequence is as follow:

1. =
2. >, >=, <, <=
3. LIKE
4. <>

For operands, the relative sequence is as follow:

1. literal alone
2. column alone, parameter alone
3. multi-operand expression
4. exact numeric data type
5. other numeric data type, temporal data type
6. character data type, NULL

Optimization technique include
(1) Put multiple conditions in order by their relative point count

(2) Constant propagation (transitive rule) is to replace column name by literal

e.g. WHERE column1 < column2 and column1 = 5

to WHERE 5 < column2 and column1 = 5

(3) Evaluate expression and replace by literal

(4) Avoid function on column.  Use LOWER instead of UPPER if no choice as UPPER will lead to lost information in some languages.

(5) For AND conditions, put the least complex first so that DBMS can eliminate the rest when evaluated to be false.  For OR, put the most likely condition first.

(6)  (Distrubtive rule) A and (B or C) is faster than (A and B) OR (A and C) as there are fewer evaluation steps.

(7) Avoid NOT

e.g. NOT (column1 > 5)

to column1 <= 5

(8) Use IN over OR

e.g. column1 IN (5,6) instead of column1 = 5 or column1 = 6

(9) DBMS will use index if LIKE pattern starts with a real character but avoid index if it starts with a wild card.

Note that LIKE 'ABC' is not same as = 'ABC' as LIKE takes trailing blanks into account and not =.

(10) Transform UNION to OR