Performance of Views

[Scott Sosna]

Has anyone done much performance testing or tuning with views? I have a query which joins four tables. When I explain the query directly (SELECT a, b, c, d FROM tab1, tab2...), the time is very good. When I explain the query through the view (SELECT * FROM view), the time goes up to 4 seconds. The query plans on each are fairly similar, except that the view's plan uses DERIVED instead of the four SIMPLE's I get with the query directly. I have tried multiple ways of writing the JOINs, I have explicitly specified an algorithm, I have different table types (MyISAM and InnoDB). No substantial difference occurs.

With most databases, my experience has been that views perform like the underlying query because the query parser is just inserting the SQL of the view (especially when you just selecting from the view). However, that doesn't seem to be the case here. I am trying to convert an application from Oracle to MySQL which relies on views, so this is very important.

TIA.
-scs