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 form
, 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 as
For example, if the table definition for
... 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
... 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
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 = 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 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.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices