How Do MySQL Views Work

How do MySQL views work?

A view works like a table, but it is not a table. It never exists; it is only a prepared SQL statement that is run when you reference the view name. IE:

CREATE VIEW foo AS
  SELECT * FROM bar

SELECT * FROM foo

...is equivalent to running:

SELECT x.* 
  FROM (SELECT * FROM bar) x

A MySQLDump will never contain rows to be inserted into a view...

Also why can't I use subqueries in my view????

That, sadly, is by (albeit questionable) design. There's numerous limitations for MySQL views, which are documented: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

So if it's just an imaginary table/prepared statement does that mean it theoretically has the same performance (or even less) as a normal table/query?

---

No.

A table can have indexes associated, which can make data retrieval faster (at some cost for insert/update). Some databases support "materialized" views, which are views that can have indexes applied to them - which shouldn't be a surprise that MySQL doesn't support given the limited view functionality (which only began in v5 IIRC, very late to the game).

Because a view is a derived table, the performance of the view is only as good as the query it is built on. If that query sucks, the performance issue will just snowball... That said, when querying a view - if a view column reference in the WHERE clause is not wrapped in a function (IE: WHERE v.column LIKE ..., not WHERE LOWER(t.column) LIKE ...), the optimizer may push the criteria (called a predicate) onto the original query - making it faster.

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.

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

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

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

MySQL how create a view to work with query parameters

As you know, SQL views cannot have parameters. But you can filter them with WHERE clauses and so forth.

This is the pattern of your task.

  CREATE VIEW something AS (
    SELECT item1, item2, item3, item4
      FROM ...
   );

Then you use that view like this

 SELECT item2, item3, item4 
   FROM something
  WHERE item1 = 'constant'

Don't worry too much about performance; the query planner is clever about optimizing queries from views. In your case it might look like this.

CREATE VIEW cuenta_contable_union AS (
    SELECT cc.code, cc.description, cc.code selector_code
      FROM cuenta_contable cc
     UNION
    SELECT cc1.code, cc1.description, cc2.code selector_code
      FROM cuenta_contable cc1
      JOIN cuenta_contable cc2 
             ON cc1.parent_cuenta_contable = cc2.id_cuenta_contable
 );

Then to use the view to choose the 32-value items you want, do this:

SELECT code, description
  FROM cuenta_contable_union
 WHERE selector_code = 32
 ORDER BY code ASC;

Next time it will be

SELECT code, description
  FROM cuenta_contable_union
 WHERE selector_code IN (10, 20, 30)
 ORDER BY code ASC;

or whatever your application requires.

If your table is large, this view might be faster if you use UNION ALL in place of UNION.

Mysql views return data when table empty

In MySQL, a view is a virtual table based on the result-set of an SQL statement.
It contains rows and columns, just like a real table in your database. The fields in a view are fields from one or more real tables in the database.

When you execute the views, they return back data from the old SQL tables. It is because your view still contains the data you run a while ago. You have forgotten to Drop your View every time you execute it. To Drop a MySQL view, try this one:

DROP VIEW view_name

---

Related Topics