Create view or use innerjoins?

Question

I have a normalized database, with foreign keys/primary keys giving one to many databases.

I plan to access this database with PHP for the basic frontend/backend display. Now, my question comes from these two exampled queries:

CREATE VIEW `view` AS
  SELECT
    functiondetails.Detail,
    functionnames.ID,
    functionnames.FunctionName,
    functionnames.Catogory
  FROM functiondetails
    INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID

or

SELECT
  functiondetails.Detail,
  functionnames.ID,
  functionnames.FunctionName,
  functionnames.Catogory
FROM functiondetails
  INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID

There is no error within the query as i've ran both without fail, but my overall question is this:

if I plan to constantly reference alot of information from my database. Wouldn't it be easier to create a view, which will then update all the time with the newly added information, or would it be in better practice to have the second query on my actual php.. Example:

$Query = $MySQli->prepare("
  SELECT
    functiondetails.Detail,
    functionnames.ID,
    functionnames.FunctionName,
    functionnames.Catogory
  FROM functiondetails
    INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID
")
$Query->execute();
$Results = $Query->fetch_results();
$Array = $Results->fetch_array(MYSQLI_ASSOC);

Or to select from my view?

$Query = $MySQLi->prepare("SELECT * FROM `view`");
$Query->execute();
$Results = $Query->fetch_results();
$Array = $Results->fetch_array(MYSQLI_ASSOC);

So which one would be a better method to use for querying my database?

I 'think' both options are essentially the same. The advantage of the view (as I see it) is that you can grant a user access to it without granting them access to your core tables. — Strawberry, Aug 26 '13 at 12:01
Related info on http://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql and http://stackoverflow.com/questions/10302615/mysql-views-performance. Hope this helps. — Anda Iancu, Aug 26 '13 at 12:03
I am always using views for simplifying my code and queries :) — , Aug 26 '13 at 12:03
@Strawberry From what I can tell, as you said. They are essentially the same, just the view is a table within your schema performing the inner join. So it's done completely server sided and not query construct side... The Join it's self, would run on the query construct side. Have to reference the specified table and report back to PHP... Either one is next to no performance hit on my webservers, it's just what will prove beneficial later on, during the expanding of this system — Daryl Gill, Aug 26 '13 at 12:05
@Akam Creating views offer the best functionality for the expanding of the system, the join? Ie. Having to look through the code and join in a few more tables; or create the actual view, and only have to reference a few more indexes from the array, which references the view? — Daryl Gill, Aug 26 '13 at 12:08
Check [this post](http://stackoverflow.com/questions/3043505/mysql-view-vs-embedded-query-which-one-is-faster). It may help! — u54r, Aug 28 '13 at 13:35

Michael Irigoyen · Accepted Answer · 2013-08-28T18:44:55.620

6

Views are an abstraction layer and the usual reason for creating an abstraction layer is to give you a tool to make your life easier.

Some of the big advantages to using views include:

Security
You can control who has access to view without granting them access to the underlying tables.
Clarification
Often times, column headers aren't as descriptive as they can be. Views allow you to add clarity to the data being returned.
Performance
Performance wise, views do not negatively hurt you. You will not, however, see a performance gain by using views either as MySQL does not support materialized views.
Ease in Coding
Views can be used to reuse complex queries with less room for user error.
Ease of Management
It makes your life easier whenever your table schema changes.

For example, say you have a table that contains homes you have for sale, homes_for_sale, but later on you decide you want that table to handle all homes you've ever had for sale/have for sale currently, all_homes. Obviously, the schema of the new table would be much different than the first.

If you have a ton of queries pulling from homes_for_sale, now you have to go through all your code and update all the queries. This opens you up to user error and a management nightmare.

The better way to address the change is replace the table with a view of the same name. The view would return the exact same schema as the original table, even though the actual schema has changed. Then you can go through your code at your own pace, if needed at all, and update your query calls.

edited Aug 28 '13 at 18:44

answered Aug 28 '13 at 18:31

Michael Irigoyen

22,513
17
89
131

The only problem left for all of the answers, is that they have focused on just the view. The embedded query done PHP side? There is no comparisons what so ever. – Daryl Gill Sep 03 '13 at 12:12
That's because a view is just a query done elsewhere (instead of within the PHP). All the advantages of using a View should be a clear indication of what the disadvantages of simply using a query in your code are. – Michael Irigoyen Sep 03 '13 at 12:15
& final question; best practices. Perform the view? Or leave it PHP sided which can be easily modified on database expansion? – Daryl Gill Sep 03 '13 at 13:08
It really comes down to the query and what you're doing with it. If I do quick `SELECT *` without much complexity, I typically don't use a view for those. However, large queries, especially those pulling from several different tables, I will use a View. – Michael Irigoyen Sep 03 '13 at 13:15
Little bit disapointed you didn't get more upvotes than you did. Nethertheless, I prefer this answer. It's well thought out & clear, from all the answers I can see the positivies from a view. Yours makes it all clearer. Thanks for the answer – Daryl Gill Sep 03 '13 at 13:20

jaczjill · Answer 2 · 2013-08-29T06:48:27.877

5

Creating View is preferable if you are:

Sure about the required columns
Want to reuse your view somewhere else as well
You like coding in abstract way. (Hiding technical details)
~~Need fast access by creating index on it.~~
Specific access to few user (point took from comments)

edited Aug 29 '13 at 06:48

answered Aug 26 '13 at 12:35

jaczjill

334
9
25

2

unless something has changed and i dont know about it you CANNOT create a index on a view in MySQL. You can do it with a materialized view in MSSQL etc. – Namphibian Aug 29 '13 at 05:31
Thanks Namphibian, I assumed that we can create materialized view in MySQL as in MSSQL. I striked that point. – jaczjill Aug 29 '13 at 06:52
I had to find out missing features the hard way. – Namphibian Aug 29 '13 at 07:13
The only problem left for all of the answers, is that they have focused on just the view. The embedded query done PHP side? There is no comparisons what so ever. – Daryl Gill Sep 03 '13 at 12:14

score 5 · Answer 3 · answered Aug 28 '13 at 19:03

You may be assuming that MySQL stores the results of a view somewhere, and updates that result as data in the underlying tables change. MySQL does not do this. Querying a view is exactly like running the query.

But it can even be worse performance than running the bare SQL query, because MySQL may accumulate the results of the base query in a temporary table, so that you can use further SQL clauses in your query against the view. I say "may" because it varies by view algorithm.

See http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html for a description of how MySQL uses either the "merge" algorithm or the "temptable" algorithm for executing a view.

If you want materialized views, there's a tool called FlexViews that maintains materialized views:

Flexviews is a materialized views implementation for MySQL. It includes a simple API that is used to create materialized views and to refresh them. The advantage of using Flexviews is that the materialized views are incrementally refreshed, that is, the views are updated efficiently by using special logs which record the changes to database tables. Flexviews includes tools which create and maintain these logs. The views created by Flexviews include support for JOINs and for all major aggregation functions.

The only problem left for all of the answers, is that they have focused on just the view. The embedded query done PHP side? There is no comparisons what so ever. — Daryl Gill, Sep 03 '13 at 12:14
SQL queries are never executed on the PHP side, regardless of querying a view or a base table. All SQL queries are executed in the database server. — Bill Karwin, Sep 04 '13 at 03:25

score 4 · Answer 4 · answered Aug 28 '13 at 18:10

A view is simply a stored text query. You can apply WHERE and ORDER against it, the execution plan will be calculated with those clauses taken into consideration. I think it would be useful if you want to keep your code "clean". What you need to keep in mind is that it is a little harder to modify the view, so if you are not quite sure about the columns, or it will change latter, stick to a query.

About performance is THE SAME!

Best regards!

Matt Urtnowski · Answer 5 · 2013-08-28T18:28:17.787

Performance wise they should be the same, but the view is better for a few practical reasons.

I prefer views because it encourages better reuse and refactoring of the complex queries by altering them in one place instead of having to copy-paste a newer version everywhere if use the query in multiple places.

Also running an update query against a view can look a lot cleaner and simpler, but be aware that you sometimes can't update multiple columns in a view that belong to different underlying tables. So if you have to update 2 different columns, you'll have to run two different update queries.

Using a view also makes sense because you offload complex database logic to the database where it belongs instead of building it into your application code.

On the downside of using a view, that can take you a little bit longer to setup if you don't have your database management tool at the ready. Also, if you have a lot of views, you'll probably have to come up with some way to organize and document them all. This gets more complex if views start building off of other views. So you'll have to plan ahead and maintain dependencies.

The only problem left for all of the answers, is that they have focused on just the view. The embedded query done PHP side? There is no comparisons what so ever. — Daryl Gill, Sep 03 '13 at 12:13

Create view or use innerjoins?

5 Answers5

Linked