Advantage of using Views in MySQL

Question

I've learned that views can be used to create custom "table views" (so to say) that aggregate related data from multiple tables.

My question is: what are the advantages of views? Specifically, let's say I have two tables:

event | eid, typeid, name
eventtype | typeid, max_team_members

Now I create a view:

eventdetails | event.eid, event.name, eventtype.max_team_members 
             | where event.typeid=eventtype.typeid

Now if I want to maximum number of members allowed in a team for some event, I could:

• use the view
• do a join query (or maybe a stored procedure).

What would be my advantages/disadvantages in each method?

Another query: if data in table events and eventtypes gets updated, is there any overhead involved in updating the data in the view (considering it caches resultant data)?

Solution 1

A view is not stored separately: when you query a view, the view is replaced with the definition of that view. So and changes to the data in the tables will show up immediately via the view.

In addition to the security feature pointed out earlier:

If you're writing a large number of queries that would perform that join, it factors out that SQL code. Like doing some operations in a function used in several places, it can make your code easier to read/write/debug.

It would also allow you to change how the join is performed in the future in one place. Perhaps a 1-to-many relationship could become a many-to-many relationship, introducing an extra table in the join. Or you may decide to denormalize and include all of the eventtype fields in each event record so that you don't have to join each time (trading space for query execution time).

You could further split tables later, changing it to a 3-way join, and other queries using the view wouldn't have to be rewritten.

You could add new columns to the table(s) and change the view to leave out the new columns so that some older queries using "select *" don't break when you change the table definitions.

Solution 2

You can restrict users to the view instead of the underlying table(s), thereby enhancing security.

Comments

[jrharshath]

"A view is not stored separately" -- so mysql does not perform caching of a views contents?

[Harold L]

Some other database engines support "materialized views" where the results of selecting from the view are cached, but MySQL does not. See the comment on materialized views here: dev.mysql.com/doc/refman/5.5/en/create-view.html

[Ken]

MySQL does have a query cache, so if your query is exactly the same (e.g., "select * from myview;"), you may well get cached results.

[Lee Avital]

They are, in fact, sometimes slower. The reason for this is that when you use certain types of views the intermediate tables are built without indexes which causes the outer SELECT statements to run slower. Read about the TEMPTABLE algorithm