Mysql VIEWS vs. PHP query

Using views adds a level of abstraction : you may later change the structure of your tables, and you will not have to change the code that displays the information about the lists, because you will still be querying the view (the view definition may change, though).

The main difference is that views are updated after each insertion, such that the data is "ready" whenever you query the view, whereas using your custom query will have MySQL compute everything each time (there is some caching, of course).

The bottom line is that if your lists are updated less frenquently than they are viewed, you will see some gains in performance in using views.

My complete answer would depend upon several things (from your application's perspective):

• do you plan to allow users to create and share such lists?
• can users create lists of any kind, or just by plugging values into existing query templates?

Assuming you have a couple of pre-defined lists to display:

Use of views offers a couple of advantages:

• your code will be cleaner
• the query to generate the views will not have to be parsed each time by mysql.

I'm not sure about this: I don't think mysql caches views as Tomasz suggests - I don't think views contain "already preparted data".

One disadvantage is that the logic involved in creating the list goes into the database instead of living in your PHP code - something I'm very averse to. In my world databases are for data, and code is for logic.

Cheers

The original question was about pros and cons, but not seeing much about disadvantages in the answers so far.

Isn't one disadvantage of views that they can give you the false comfort of running a simple query?For instance, SELECT username FROM myview WHERE id='1'That looks simple, but what if "myview" is a really complex SELECT... Perhaps even built on other views? You end up having a simple-looking query that, in the background, takes a whole lot more work than if you had written your query from the ground up.

I've been experimenting with views, and despite the benefits, have not yet been fully sold.I'd be interested in hearing what others perceive about the disadvantages of Views, rather than just the party line about why views are so great. Might still make the switch, but would like to understand more about performance.