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:
- View the data without storing the data into the object.
- Restrict the view of a table i.e. can hide some of columns in the tables.
- Join two or more tables and show it as one object to user.
- Restrict the access of a table so that nobody can insert the rows into the table.
See these useful links:
- Performance of VIEW vs. SQL statement
- Is a view faster than a simple query?
- Mysql VIEWS vs. PHP query
- Are MySql Views Dynamic and Efficient?
- Materialized View vs. Tables: What are the advantages?
- Is querying over a view slower than executing SQL directly?
- A workaround for the performance problems of TEMPTABLE views
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
andBLOBs
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 saySELECT *
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 equivalentSELECT
.Good
INDEXes
are the main key to performance. Having the columns of theWHERE
andON
clauses in indexes is a simplistic first step. More: http://mysql.rjweb.org/doc.php/index_cookbook_mysqlThe 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
How to Copy Data from One Table to Another in Postgres Using Copy Command
How to Avoid "Table Mutating" Errors
General Rules for Simplifying SQL Statements
Selecting Distinct Combinations
Transfer Data Between Databases with Postgresql
Find Groups with Matching Rows
Fetch a Record on Maximum Date of Every Month
Understanding the Differences Between Cube and Rollup
Any Detailed and Specific Reasons for Why Mongodb Is Much Faster Than SQL Dbs
How to Delete Duplicates from a Database Table Based on a Certain Field
Vb.Net Escape Reserved Keywords in SQL Statement
How to Create Delphi 4 Structure to Map Column Names in Xls to Column Names in SQL
How to Concatenate Text from Multiple Rows into a Single Text String in Oracle Server
SQL Server Max Statement Returns Multiple Results
How to Make a SQL Query for Last Transaction of Every Account