Databases Reference
In-Depth Information
mysql>
SELECT c.day, c.cost, s.sales
->
FROM temp.cost_per_day_1234 AS c
->
INNER JOIN sales.sales_per_day AS s USING(day)
->
WHERE day BETWEEN '2007-01-01' AND '2007-01-31';
What really happens in this query is that the server executes the view and places the
result into a temporary table, then joins the
sales_per_day
table against this temporary
table. The
BETWEEN
restriction in the
WHERE
clause is not “pushed into” the view, so the
view will create a result set for all dates in the table, not just the one month desired.
The temporary table also lacks any indexes. In this example, this isn't a problem: the
server will place the temporary table first in the join order, so the join can use the index
on the
sales_per_day
table. However, if we were joining two such views against each
other, the join would not be optimized with any indexes.
Views introduce some issues that aren't MySQL-specific. Views might trick developers
into thinking they're simple, when in fact they're very complicated under the hood. A
developer who doesn't understand the underlying complexity might think nothing of
repeatedly querying what looks like a table but is in fact an expensive view. We've seen
cases where an apparently simple query produced hundreds of lines of
EXPLAIN
output
because one or more of the “tables” it referenced was actually a view that referred to
many other tables and views.
You should always measure carefully if you're trying to use views to improve perfor-
mance. Even
MERGE
views add overhead, and it's hard to predict how a view will impact
performance. Views actually use a different execution path within the MySQL opti-
mizer, one that isn't tested as widely and might still have bugs or problems. For that
reason, views don't seem quite as mature as we'd like. For example, we've seen cases
where complex views under high concurrency caused the query optimizer to spend a
lot of time in the planning and statistics stages of the query, even causing server-wide
stalls, which we solved by replacing the view with the equivalent SQL. This indicates
that views—even those using the
MERGE
algorithm—don't always have an optimal
implementation.
Limitations of Views
MySQL does not support the materialized views that you might be used to if you've
worked with other database servers. (A
materialized view
generally stores its results in
an invisible table behind the scenes, with periodic updates to refresh the invisible table
from the source data.) MySQL also doesn't support indexed views. You can emulate
materialized and/or indexed views by building cache and summary tables, however.
You use Justin Swanhart's Flexviews tool for this purpose; see
Chapter 4
for more.
MySQL's implementation of views also has a few annoyances. For example, MySQL
doesn't preserve your original view SQL, so if you ever try to edit a view by executing
SHOW CREATE VIEW
and changing the resulting SQL, you're in for a nasty surprise. The
