Databases Reference
In-Depth Information
There are obvious performance and query optimization problems with this approach.
A better way to implement views is to rewrite a query that refers to the view, merging
the view's SQL with the query's SQL. The following example shows how the query
might look after MySQL has merged it into the view definition:
mysql>
SELECT Code, Name FROM Country
->
WHERE Continent = 'Oceania' AND Name = 'Australia';
MySQL can use both methods. It calls the two algorithms
MERGE
and
TEMPTABLE
,
4
and
it tries to use the
MERGE
algorithm when possible. MySQL can even merge nested view
definitions when a view is based upon another view. You can see the results of the query
rewrite with
EXPLAIN EXTENDED
, followed by
SHOW WARNINGS
.
If a view uses the
TEMPTABLE
algorithm,
EXPLAIN
will usually show it as a
DERIVED
table.
Figure 7-1
illustrates the two implementations.
Figure 7-1. Two implementations of views
MySQL uses
TEMPTABLE
when the view definition contains
GROUP BY
,
DISTINCT
, aggregate
functions,
UNION
, subqueries, or any other construct that doesn't preserve a one-to-one
relationship between the rows in the underlying base tables and the rows returned from
4. That's “temp table,” not “can be tempted.” MySQL's views don't fast for 40 days and nights in the
wilderness, either.
