Mysql VIEWS queries against PHP

Question

I am working on a web application that includes creating a list of restaurants on various lists, such as "Joe must visit places." Now for each restaurant and list I display on a website that calculates

• Calculation of the popularity of the restaurant
• List Popularity
• Number of Restaurant Lists Present in

I am currently using MySQL instructions for PHP for this, but I plan to upgrade to MySQL VIEWS and make a simple select statement in PHP ...

my question is, What is the advantage / disadvantage of using VIEWS over writing SQL queries in PHP?

Answer 1

Using views adds an abstraction layer : you can later change the structure of your tables, and you don’t have to change the code that displays information about the lists, because you will still have a request for the view (the definition of the view can change, though).

The main difference is that the views are updated after each insert, so the data is "ready" whenever you request a view, while using a custom query, MySQL calculates everything every time (for example, there is caching).

The bottom line is that if your lists are updated less openly than they are viewed, you will see some performance benefits when using views.

Answer 2

My complete answer will depend on several things (from the point of view of your application):

• Do you plan to allow users to create and distribute such lists?
• can users create lists of any type or just connect values ​​to existing query templates?

Assuming you have a couple of predefined lists to display:

Using views offers several advantages:

• your code will be cleaner
• a view creation request should not be parsed every time by mysql.

I am not sure about this: I don’t think mysql caches the views as Tomas suggests - I don’t think that the views contain "already prepared data".

One of the drawbacks is that the logic associated with creating the list gets into the database instead of living in your PHP code, which I really dislike. In my world, databases are for data, and code is for logic.

Greetings

Answer 3

The original question concerned the pros and cons, but so far I have not seen many shortcomings in the answers.

Is it not a disadvantage of the views that they can give you false comfort to run a simple request? For example, SELECT username FROM myview WHERE id = '1' It looks simple, but what if "myview" is really a complex SELECT ... Perhaps even built on other views? As a result, you have a simple query that in the background requires a lot more work than if you wrote your query from scratch.

I experimented with performances and, despite the benefits, was not yet fully sold. I would be interested to know that others perceive shortcomings , and not just the party line on why opinions are so great. It can still make a switch, but I would like to know more about performance.

Answer 4

If these tables that you are trying to view are not subject to frequent changes, you are definitely getting performance because you are simply making a simple selection from the data that has already been prepared. But keep in mind that this point of view is not something that is done "once and for all" - each change in the contents of one of the tables will cause the database engine to "view the update", so another request (the request that you create from) should be triggered by changes that have been made. Summarizing:

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

Answer 5

Disadvantages:

• In my opinion, databases are used for the data layer, and you should not enter a business code in them. This reduces maintainability, and this contradicts the pure separation of layers. The same applies to business code and calculations in java scripts of web pages. For java script, it is even more serious, as it creates security risks. Versioning for code inside the database is also another problem.

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

• Moving an application from one database engine to another will be much more difficult (because in addition to simple queries, stored procedures / views, etc., may also be different). If the database refers only to data, then the level of abstraction may allow us to change the database mechanism (at least to some extent).

Benefits:

• A slight increase in performance (since data is not output from the database for processing, it is processed directly in the database).

• The code will seem cleaner (since the dirt is hidden inside the database views, stored procedures, etc.).