MySQL Views - When to Use & When Not To

MySQL Views - When to use & when not to

This mysql-forum-thread about indexing views gives a lot of insight into what mysql views actually are.

Some key points:

• A view is really nothing more than a stored select statement
• The data of a view is the data of tables referenced by the View.
• creating an index on a view will not work as of the current version
• If merge algorithm is used, then indexes of underlying tables will be used.
• The underlying indices are not visible, however. DESCRIBE on a view will show no indexed columns.

MySql views performance

It Depends.

It totally depends on what you are viewing through view. But most probably reducing your effort and giving higher performance. When SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

A view is not compiled. Its a virtual table made up of other tables. When you create it, it doesn't reside somewhere on your server. The underlying queries that make up the view are subject to the same performance gains or dings of the query optimizer. I've never tested performance on a view VS its underlying query, but i would imagine the performance may vary slightly. You can get better performance on an indexed view if the data is relatively static. This may be what you are thinking maybe in terms of "compiled".

Advantages of views:

1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restrict the access of a table so that nobody can insert the rows into the table.

See these useful links:

1. Performance of VIEW vs. SQL statement
2. Is a view faster than a simple query?
3. Mysql VIEWS vs. PHP query
4. Are MySql Views Dynamic and Efficient?
5. Materialized View vs. Tables: What are the advantages?
6. Is querying over a view slower than executing SQL directly?
7. A workaround for the performance problems of TEMPTABLE views
8. See performance gains by using indexed views in SQL Server

MYSQL View vs Select Performance and Latency

Views don't modify the performance of your database. The performance is the same as the select it includes. The views are not used to increment performance or deliver pages faster, they are used for two things:

• The use of a view lets you grant permisions to users on different tables so they don't see all information in the table (they only see what you want and in the way you want)
• If you use the same select query in different places in your code, if you need to modify it, you can just modify the view and not every query in your code

What are views in MySQL?

Normal Views are nothing more then queryable queries.

Example:

You have two tables, orders and customers, orders has the fields id, customer_id, performance_date and customers has id, first_name, last_name.

Now lets say you want to show the order id, performance date and customer name together instead of issuing this query:

SELECT    o.id as order_id, c.first_name + ' ' + c.last_name as customer_name,
          o.performance_date 
FROM      orders o inner join customers c

you could create that query as a view and name it orders_with_customers, in your application you can now issue the query

SELECT   *
FROM     orders_with_customer

One benefit is abstraction, you could alter the way you store the customers name, like inlcuding a middle name, and just change the views query. All applications that used the view continue to do so but include the middle name now.

Advantage of using Views in MySQL

A view is not stored separately: when you query a view, the view is replaced with the definition of that view. So and changes to the data in the tables will show up immediately via the view.

In addition to the security feature pointed out earlier:

If you're writing a large number of queries that would perform that join, it factors out that SQL code. Like doing some operations in a function used in several places, it can make your code easier to read/write/debug.

It would also allow you to change how the join is performed in the future in one place. Perhaps a 1-to-many relationship could become a many-to-many relationship, introducing an extra table in the join. Or you may decide to denormalize and include all of the eventtype fields in each event record so that you don't have to join each time (trading space for query execution time).

You could further split tables later, changing it to a 3-way join, and other queries using the view wouldn't have to be rewritten.

You could add new columns to the table(s) and change the view to leave out the new columns so that some older queries using "select *" don't break when you change the table definitions.

Does MySQL views load all data of all joined tables to memory

• Some TEXTs and BLOBs are stored "off-record". So, if you don't need a column, do not ask for it -- else it will incur (perhaps) an extra disk hit. Phrased another way, it is potentially bad for performance to say SELECT * instead if specifying just the columns you need.

• MySQL VIEWs are syntactic sugar. They rarely (maybe ever) help performance. In some situations, they are slower than the equivalent SELECT.

• Good INDEXes are the main key to performance. Having the columns of the WHERE and ON clauses in indexes is a simplistic first step. More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

• The best indexing for a many:many relationship: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

• Each query incurs some overhead. So, it is usually better to use as few queries as possible to perform a task. For example, using one query to fetch a set of "ids", then building another query to act on the corresponding rows, is very likely to be slower than a combined query.

• If you need 7-8 tables to gather the info, consider whether the data is "over-normalized".

Consider providing a single SELECT plus the relevant SHOW CREATE TABLEs. We can talk through that with less 'hand-waving'.

Is there a way in MySQL to update a view with data from a table that is not covered by the view?

As per Solarflare -

A view is just the result of a query. The "content" of a view changes
when you change the underlying data (e.g. table test1) - and it
doesn't change if you don't change the underlying data

So, when I perform, test1 = test1.append(test2), the test_view that I have created from the underlying test1 dataframe gets updated automatically.

phpMyAdmin Tables vs. MySQL Views

You are asking about visual style. Neither tables nor views have a particular style. The tool you use -- the command line, PHPMySQLAdmin, MySQL Workbench, or whatever -- can display them however the developer likes.

For convenience, views and tables will likely look very similar or the same in most tools, because they have similar structures (but not the same functions or purposes). But how they look is up to the developer of the tool.

---

Related Topics