In the last episode (Aug 26), Karl Larsen said:
> I was working with my version 5 and made a couple of views which
> are very useful. Looking in the db I was able to see the VIEW's saved
> as TABLE :-)
What command did you run to determine this? "SHOW TABLES" does list
them but that's to be expected since views act like tables. "SHOW
TABLE STATUS" and selecting from information_schema.tables both clearly
distinguish tables from views.
> This was a surprise and not sure if this is the expected result or
> not. Then I did some SELECT that involved the VIEW and it does work a
> lot like another TABLE, but it can and mine does take data from many
> TABLE's into a VIEW.
>
> You can write a SELECT or even another VIEW using a VIEW. But if
> you keep track of the time used by a query, it starts to get too long
> if you use a SELECT of a VIEW that has within it another VIEW.
Mysql's optimization of views is currently very rudimentary. If your
view is simple (adding a computed column, etc) it directly modifies
your query to match the view and runs it on the view's parent table.
Otherwise it has to create a temporary table containing the entire
view's contents and then run your query on that. Nested views using
temptables could certainly be very slow.
http://dev.mysql.com/doc/refman/5.0/en/create-view.htmlhttp://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html--
Dan Nelson
dnelson@stripped

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.