CODEWITHSUNDEEP
phpmysqlperformance
Daryl Gill
Daryl Gill

Reputation: 5524

Create view or use innerjoins?

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?

Upvotes: 12

Views: 1725

Answers (5)

jaczjill
jaczjill

Reputation: 334

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)

Upvotes: 5

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 5

Michael Irigoyen
Michael Irigoyen

Reputation: 22947

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:

  1. Security
    You can control who has access to view without granting them access to the underlying tables.

  2. Clarification
    Often times, column headers aren't as descriptive as they can be. Views allow you to add clarity to the data being returned.

  3. 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.

  4. Ease in Coding
    Views can be used to reuse complex queries with less room for user error.

  5. 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.

Upvotes: 6

Matt Urtnowski
Matt Urtnowski

Reputation: 2566

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.

Upvotes: 3

JGutierrezC
JGutierrezC

Reputation: 4513

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!

Upvotes: 4

Related Questions