469,568 Members | 1,445 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Views and Why We Love Them

pbmods
5,821 Expert 4TB
Today I'd like to talk about a new feature introduced in MySQL 5: Views.

Views are like snapshots of queries. You can save any query as a view, even complex queries with joins and subqueries, and MySQL will save the results of that query in a format that can be searched (and in some cases, modified) just like a table. But unlike tables, you never have to worry about updating the data in your view; MySQL handles all of the details for you!

For the purposes of an example, let's pretend you built a database for Wally's Widget Works. The good folks at Wally's decided that they needed to keep track of the different kinds of widget that they produce. To make it easier to sort the different widgets, they have decided to give each widget one or more 'tags' that describes some aspect of that widget's function or purpose.

So you start out with a database that looks something like this:
  • Data_Widgets (`widgetid` serial, `desc` varchar)
  • Data_Tags (`tagid` serial, `desc` varchar)
  • Map_WidgetTag (`widgetid` bigint, `tagid` bigint)

Since each Widget can have more than one tag, and each tag could be associated with more than one Widget, we have a database design professor's favorite topic of lecture: a many-to-many relationship.

Let's suppose we wanted to find all the Widgets that matched the tag, "lifesupport".

Expand|Select|Wrap|Line Numbers
  1. SELECT `Data_Widgets`.* FROM (`Data_Tags` LEFT JOIN `Map_WidgetTag` USING(`tagid`) LEFT JOIN `Data_Widgets` USING(`widgetid`)) WHERE `Data_Tags`.`desc` = 'lifesupport' ORDER BY `desc` ASC;
  2.  
Oh, and a little further down the page, we want to get all the tags for widget #15806:

Expand|Select|Wrap|Line Numbers
  1. SELECT `Data_Tags`.* FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`.`widgetid` = '15806' ORDER BY `desc` ASC;
  2.  
Oh, and a little further, we need all the tags that are associated with any widget whose description starts with 'Green':

(well, you get the idea; there's a lot of queries, and they're all really similar)

Now, let's say that the BOSS comes around and says, "Hey, that looks really good, but we don't need to worry about widgets 0-999. Those belong to the accounting department, and they have their own system."

So off you go to add a "AND `widgetid` > 999" to *every* *single* *one* *of* *your* *queries*.

And you also missed one, which caused a great deal of embarrassment at the company picnic the following month.

There's got to be a better way.

And there is! Enter views.

Expand|Select|Wrap|Line Numbers
  1. CREATE VIEW `View_WidgetTags` AS SELECT `Data_Widgets`.`widgetid`, `Data_Widgets`.`desc` AS `widgetdesc`, `Data_Tags`.`tagid`, `Data_Tags`.`desc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`.`widgetid` > 999;
  2.  
Going back to our exmples, to find all "lifesupport" widgets:

Expand|Select|Wrap|Line Numbers
  1. SELECT `widgetid`, `widgetdesc` FROM `View_WidgetTags` WHERE `tagdesc` = 'lifesupport' ORDER BY `widgetdesc` ASC;
  2.  
And to find all tags for widget #15806:

Expand|Select|Wrap|Line Numbers
  1. SELECT `tagid`, `tagdesc` FROM `View_WidgetTags` WHERE `widgetid` = '15806' ORDER BY `tagdesc` ASC;
  2.  
And incidentally:

Expand|Select|Wrap|Line Numbers
  1.     SELECT * FROM `View_WidgetTags` WHERE `widgetdesc` LIKE 'Green%' ORDER BY `widgetdesc`, `tagdesc` ASC;
  2.  
Remember that the view is a hybrid query/table; you define it like a query, but you access it like a table. When you go to SELECT data from your view, MySQL has already compiled the data that matches the query you used to define the view, so your SELECT query will execute even faster!

And when the BOSS stops by your cube and says, "Hey, Murray from accounting says that they're giving us their widgets, so we need to include them in our searches again," you smile and walk over to your terminal and work your magic:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE VIEW `View_WidgetTags` AS SELECT `Data_Widgets`.`widgetid`, `Data_Widgets`.`desc` AS `widgetdesc`, `Data_Tags`.`tagid`, `Data_Tags`.`desc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`));
  2.  
And instantly, without having to change a single query in your PHP code (or what-have-you), *all* your queries return the previously-forbidden widgets!

For more information on views (especially the quirks when it comes to UPDATEing or DELETEing data in a view), check out the MySQL manual page here:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
May 6 '07 #1
1 9035
bucabay
18
How is this better than creating a table based on a select query?

CREATE TABLE `View_WidgetTags` SELECT `Data_Widgets`.`widgetid`, `Data_Widgets`.`desc` AS `widgetdesc`, `Data_Tags`.`tagid`, `Data_Tags`.`desc` AS `tagdesc` FROM (`Data_Widgets` LEFT JOIN `Map_WidgetTag` USING(`widgetid`) LEFT JOIN `Data_Tags` USING(`tagid`)) WHERE `Data_Widgets`.`widgetid` > 999;

And then running queries on that table?

---

Answered my own question with a simple test. I tried updating the table which the VIEW selects from and the VIEW's result is updated also.

The CREATE VIEW FROM QUERY "caches" the QUERY not the SQL Result Set. So if the Original table in the QUERY is updated, your VIEW table is updated, and yet you can treat a VIEW just like a table, set permissions etc...

very cool.
Jun 9 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Mike N. | last post: by
3 posts views Thread by dbtoo_dbtoo | last post: by
224 posts views Thread by VB6 User | last post: by
33 posts views Thread by Peter | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.