7.2.1.2 Eliminating Dead Code

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.