How do MySQL views work?

by

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.

More Related Contents:

  1. INNER JOIN ON vs WHERE clause
  2. MySQL: Alternatives to ORDER BY RAND()
  3. MySQL LIKE IN()?
  4. Auto Increment after delete in MySQL
  5. Unioning two tables with different number of columns
  6. NULL in MySQL (Performance & Storage)
  7. In MySQL queries, why use join instead of where?
  8. MySQL function to find the number of working days between two dates
  9. MySQL Trigger to prevent INSERT under certain conditions
  10. Importance of varchar length in MySQL table
  11. Stored Procedure with optional “WHERE” parameters
  12. mySQL :: insert into table, data from another table?
  13. Return 0 if field is null in MySQL
  14. SQLite – ORDER BY RAND()
  15. SQL – How to transpose?
  16. How to search multiple columns in MySQL?
  17. MySQL – Selecting data from multiple tables all with same structure but different data
  18. Can a stored procedure/function return a table?
  19. SQL SELECT to get the first N positive integers
  20. Adding a new SQL column with a default value
  21. Selecting all corresponding fields using MAX and GROUP BY
  22. MySQL full text search for words with three or less letters
  23. MySQL’s alternative to T-SQL’s WITH TIES
  24. The difference between ‘AND’ and ‘&&’ in SQL
  25. How to transform vertical data into horizontal data with SQL?
  26. I want to copy table contained from one database and insert onto another database table
  27. How to find all IDs of children recursively?
  28. How to implement a Keyword Search in MySQL?
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment