21

I am working on a web application which involves create list of Restaurants in various lists like "Joe's must visit places". Now for each Restaurant and list, I have display on website which calculates

  • Calculating popularity of a Restaurant
  • Popularity of a list
  • Number of lists a Restaurant is present in

Currently I am using MySQL statements in PHP for this but planning to switch to MySQL VIEWS and do a simple select statement in PHP...

my question is, What is Advantage/Disadvantage of using VIEWS over writing sql queries in PHP?

Tarun
  • 5,374
  • 4
  • 22
  • 32
  • 1
    This question is IMO, too broad, what kind of advantage are you looking at? Code maintainability? Query speed? or what? – Andreas Wong Dec 13 '10 at 08:24
  • why dont you post your ER model or schema then we can suggest ways of optimising it – Jon Black Dec 13 '10 at 10:36
  • I wanted to know in a broader perspective, need to use this in multiple projects. – Tarun Dec 13 '10 at 12:47
  • 3
    Use views. end of story. Your life will be so much easier. Views make for cleaner code, a single point where you can edit most of all your database queries. in PHP make sure you reference your columns by name and not by number to keep the views editable. I switched to views, I had some complex queries that run for pages. If I had that in php i'd have gone crazy – Jason Jan 18 '11 at 05:35
  • thanks Jason, I switched my architecture to use views instead of sql queries in php – Tarun Jan 18 '11 at 06:06

5 Answers5

16

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.

Wookai
  • 20,883
  • 16
  • 73
  • 86
  • 1
    "views are updated after each insertion" -- how do you mean? Do you imply that views are computed and cached before hand? – jrharshath Dec 13 '10 at 10:16
  • Maybe you can look at http://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html if you use MySQL. You have two ways, a temp table and an inline mode. In both the view SQL is executed, in `MERGE` it is "pasted" inside the calling SQL but with `TEMPTABLE` a temporary table is created and filled with the query data (so locks can be released). – PhoneixS Aug 31 '16 at 12:52
5

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.

zx81
  • 41,100
  • 9
  • 89
  • 105
4

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

jrharshath
  • 25,975
  • 33
  • 97
  • 127
  • Where have I said this? :) I said only that frequent changes to database (ie. inserts to the tables that view consists of) would make view constantly updating and thus performance is... gone. And ok, if you mean "view cache" as caching view query - yes, compiled query is stored and used as such when user tries to fetch data from view. Views do not "contain" data, I meant only the fact, that database engine holds the prepared data to use for view, it is not computed "on the fly", per request. – Tomasz Kowalczyk Dec 13 '10 at 08:37
  • hmm. I surely must have misunderstood. – jrharshath Dec 13 '10 at 10:15
  • 1
    @TomaszKowalczyk can you link me to where you see that data is prepared for use in a view? It seems from reading around that the queries are run like usual in the background (with joins, unions, etc), so there aren't actually any performance benefits. EDIT: I think you're talking about Temptable Views. They don't have indexes so a large table will be very slow to query. I'm not sure it's worth it, unless you only have a small number of rows. – timetofly Apr 17 '14 at 02:34
  • Maybe you can look at http://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html if you use MySQL. You have two ways, a temp table and an inline mode. In both the view SQL is executed, in `MERGE` it is "pasted" inside the calling SQL but with `TEMPTABLE` a temporary table is created and filled with the query data (so locks can be released). – PhoneixS Aug 31 '16 at 12:48
3

If that tables you are trying to make view from are not subject to a frequent change, definitely you gain performance, as you are only doing simple select from already prepared data. But be aware of the fact, that view is not something that is made "once and forever" - every change of a content of one of the tables will make database engine do "view refreshing", so another query (query you are making view from) must be called to taki into account changes that were made. To sum up:

Infrequent changes? Performance. Frequent / constant changes (community adding, commenting, rating your restaurants) - better go with SQL queries.

Tomasz Kowalczyk
  • 10,472
  • 6
  • 52
  • 68
0

Disadvantages:

  • In my opinion databases are used for data layer and it is not that proper to put business code inside them. It both reduces maintainability and it contradicts clean separation of layers. The same applies to including business code and calculations in java scripts of web pages. For java script it is even more serious since it creates security threats. Source control for the code inside database is also another issue.

  • Now that code is inside database, the security and access complications (to views and stored procedures) is also added.

  • Migrating an application from one database engine to another will be much more difficult (since in addition to simple queries the stored procedures/views etc. are possibly different too). If the database is only about data then an abstraction layer could allow changing the database engine (at least on at some extent).

Advantages:

  • Slight performance gains (since data is not coming out of the database for processing, it is processed right inside the database).

  • Code will seem cleaner (since the dirtiness is hidden inside the database views, stored procedures etc.).

wmac
  • 1,023
  • 1
  • 16
  • 34