A MySQL constant is something more than a mere literal in the query. It can also be the contents of a constant table, which is defined as follows:
A table with zero rows, or with only one row
A table expression that is restricted with a
WHERE
condition, containing expressions of the formcolumn =
, for all the columns of the table's primary key, or for all the columns of any of the table's unique keys (provided that the unique columns are also defined asconstant
NOT NULL
).
For example, if the table definition for
Table0
contains
... PRIMARY KEY (column1,column2)
then this expression
FROM Table0 ... WHERE column1=5 AND column2=7 ...
returns a constant table. More simply, if the table definition
for Table1
contains
... unique_not_null_column INT NOT NULL UNIQUE
then this expression
FROM Table1 ... WHERE unique_not_null_column=5
returns a constant table.
These rules mean that a constant table has at most one row value. MySQL will evaluate a constant table in advance, to find out what that value is. Then MySQL will “plug” that value into the query. Here's an example:
SELECT Table1.unique_not_null_column, Table2.any_column
FROM Table1, Table2
WHERE Table1.unique_not_null_column = Table2.any_column
AND Table1.unique_not_null_column = 5;
When evaluating this query, MySQL first finds that table
Table1
— after restriction with
Table1.unique_not_null_column
— is a
constant table according to the second definition above. So it
retrieves that value.
If the retrieval fails (there is no row in the table with
unique_not_null_column
= 5), then the
constant table has zero rows and you will see this message if
you run EXPLAIN
for the statement:
Impossible WHERE noticed after reading const tables
Alternatively, if the retrieval succeeds (there is exactly one
row in the table with
unique_not_null_column
= 5), then the
constant table has one row and MySQL transforms the query to
this:
SELECT 5, Table2.any_column
FROM Table1, Table2
WHERE 5 = Table2.any_column
AND 5 = 5;
Actually this is a grand-combination example. The optimizer does some of the transformation because of constant propagation, which we described earlier. By the way, we described constant propagation first because it happens before MySQL figures out what the constant tables are. The sequence of optimizer steps sometimes makes a difference.
Although many queries have no constant-table references, it should be kept in mind that whenever the word constant is mentioned hereafter, it refers either to a literal or to the contents of a constant table.
See:
/sql/sql_select.cc
,
make_join_statistics()
.