List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 27 2006 3:15am
Subject:Re: How a VIEW is stored
View as plain text  
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.html
http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html

-- 
	Dan Nelson
	dnelson@stripped
Thread
How a VIEW is storedKarl Larsen26 Aug
  • Re: How a VIEW is storedDan Nelson27 Aug