Databases Reference
In-Depth Information
Performance Implications of Views
Most people don't think of using views to improve performance, but in some cases they
can actually enhance performance in MySQL. You can also use them to aid other per-
formance improvements. For example, refactoring a schema in stages with views can
let some code continue working while you change the tables it accesses.
You can use views to implement column privileges without the overhead of actually
creating those privileges:
CREATE VIEW public.employeeinfo AS
SELECT firstname, lastname -- but not socialsecuritynumber
FROM private.employeeinfo;
GRANT SELECT ON public.* TO public_user;
You can also sometimes use pseudotemporary views to good effect. You can't actually
create a truly temporary view that persists only for your current connection, but you
can create a view under a special name, perhaps in a database reserved for it, that you
know you can drop later. You can then use the view in the
FROM
clause, much the same
way you'd use a subquery in the
FROM
clause. The two approaches are theoretically the
same, but MySQL has a different codebase for views, so performance can vary. Here's
an example:
-- Assuming 1234 is the result of CONNECTION_ID()
CREATE VIEW temp.cost_per_day_1234 AS
SELECT DATE(ts) AS day, sum(cost) AS cost
FROM logs.cost
GROUP BY day;
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);
DROP VIEW temp.cost_per_day_1234;
Note that we've used the connection ID as a unique suffix to avoid name clashes. This
approach can make it easier to clean up in the event that the application crashes and
doesn't drop the temporary view. See
“Missing Temporary Tables” on page 502
for
more about this technique.
Views that use the
TEMPTABLE
algorithm can perform very badly (although they might
still perform
better
than an equivalent query that doesn't use a view). MySQL executes
them as a recursive step in optimizing the outer query, before the outer query is even
fully optimized, so they don't get a lot of the optimizations you might be used to from
other database products. The query that builds the temporary table doesn't get
WHERE
conditions pushed down from the outer query, and the temporary table does not have
any indexes.
5
Here's an example, again using the
temp.cost_per_day_1234
view:
5. This will be improved in MySQL 5.6, which is unreleased at the time of writing.
