Re: Views and subqueries
Date: Mon, 19 Oct 2015 22:47:08 +0200
Message-ID: <cf1gfc-io6.ln1_at_xl.homelinux.org>
ram_at_zedat.fu-berlin.de (Stefan Ram) wrote:
> It might be possible that I do not understand the
> implementation of views in MySQL!
No shit, Sherlock!
> IIRC, in MySQL, views cannot contain subqueries.
Views never "contain" queries.
Not for any sane meaning of the word "contain".
> And some parties say that views might degrade performance.
This is a very vague claim. It is true that a query on a view might use a different execution plan that the equivalent query on the relevant base tables. And the plan for the view can be slower (or sometimes) faster. Much the same as different formulations of equivalent queries (i.e. subquery vs. JOIN) can use different plans.
> But, if I had to implement views, I'd implement subqueries
> first, then implement view by inserting a view that is used
> as a subquery recursively until there are no more views used.
> Then do a »global« optimization of the resulting query.
Congratulations! You just reinvented the MERGE algorithm for processing a query on a view. MySQL has this since ... err ... always?
https://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html
> when implemented as
> above, a view should never be slower than a subquery.
Only if you have a perfect optimizer. You are welcome to write one. Dont be shy, apply today!
Use the source, Luke! Received on Mon Oct 19 2015 - 22:47:08 CEST