A transformation takes place for conditions that are always true, for example:
WHERE 0=0 AND column1='y'
In this case, the first condition is removed, leaving
WHERE column1='y'
See:
/sql/sql_select.cc
,
remove_eq_conds()
.
A transformation also takes place for conditions that are
always false. For example, consider this
WHERE
clause:
WHERE (0 = 1 AND s1 = 5) OR s1 = 7
Since the parenthesized part is always false, it is removed, reducing this expression to
WHERE s1 = 7
In some cases, where the WHERE
clause
represents an impossible condition, the optimizer might
eliminate it completely. Consider the following:
WHERE (0 = 1 AND s1 = 5)
Because it is never possible for this condition to be true,
the EXPLAIN
statement will show the words
Impossible WHERE
. Informally, we at MySQL
say that the WHERE
has been
“optimized away”.
If a column cannot be NULL
, the optimizer
removes any non-relevant IS NULL
conditions. Thus,
WHERE not_null_column IS NULL
is an always-false situation, and
WHERE not_null_column IS NOT NULL
is an always-true situation — so such columns are also
eliminated from the conditional expression. This can be
tricky. For example, in an OUTER JOIN
, a
column which is defined as NOT NULL
might
still contain a NULL
. The optimizer leaves
IS NULL
conditions alone in such
exceptional situations.
The optimizer will not detect all Impossible
WHERE
situations — there are too many
possibilities in this regard. For example:
CREATE TABLE Table1 (column1 CHAR(1));
...
SELECT * FROM Table1 WHERE column1 = 'Canada';
The optimizer will not eliminate the condition in the query,
even though the CREATE TABLE
definition
makes it an impossible condition.