Re: Performance of views

Tom Lane wrote:
> "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
>> Note that, at least in older versions, MySQL completely materialized a
>> temporary table from a view, then used that for the view. This is
>> horribly inefficient, and results in a lot of people thinking views
>> are slow. Not sure if this has been addressed in MySQL yet, don't
>> really care anymore, since I rarely use mysql for anything anymore.
>
> Some simple experiments with mysql 5.0.67 suggest that this meme is
> obsolete there too. I found some cases where it looks like we optimize
> a bit better than they do, but for simple views you seem to get the
> same plan as if you'd written out the equivalent query in-line.
>

This is true of simple queries, but as soon as your query starts
becoming "complex" (e.g. involving subselects) it just dumps the result
of the view query into a temporary table and uses that. (Tested with
5.0.67 too).