What is the difference between PostgreSQL's materialized views and MySQL's views?
VIEW is a stored query. When you select from it, you essentially run the query.
CREATE VIEW foo AS SELECT col1,col2 FROM bar;
SELECT foo.* FROM ( SELECT col1,col2 FROM bar ) AS foo;
A materialized view is more or less a view that is cached (or materialized) to disk,
CREATE IF NOT EXISTS TABLE foo AS SELECT col1,col2 FROM bar;
With the added bonus that, at least in PostgresSQL, there is a command
REFRESH MATERIALIZED VIEW that you can feed to it the name of the view, and it'll regenerate it from scratch:
REFRESH MATERIALIZED VIEW foo;
This applies to MySQL only...
MySQL does not have materialized views.
MySQL combines the
VIEW into the query that is using it. This means that optimization can occur dynamically for each query, instead of being statically done when the
VIEW is defined. There are two main optimizations:
TEMPTABLE. See 5.7 View Algorithms.
MERGE as blending the
VIEW definition with the query in question, then optimizing.
TEMPTABLE as punting on
MERGE and simply creating a temporary table containing the results of the
VIEW (as if it were a standalone
SELECT), then using that temp table in the query in question.
The bottom line is that a
VIEW is "syntactic sugar", not a performance tool. Neither merge, nor temptable, can deliver better performance than you could by manually doing the equivalent.
"Materialized Views" probably do not exist in MySQL because there is no single 'best' way to do them. In my discussion on Summary Tables, I list six ways for augmenting materialized views for MySQL, each with pros and cons. (Note: MySQL provides no syntax for materialized views; you do all the work.)
A materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.
PostgreSQL supports materialized views and temporary tables while MySQL supports temporary tables but does not support materialized views. Difference being that MySQL view uses a query to pull data from the underlying tables while PostgreSQL materialized view is a table on disk that contains the result set of a query.
In RDBM model, a view is a virtual table representing the result of a database query. Whenever a query or an update addresses an ordinary view's virtual table, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach: the query result is cached as a concrete ("materialized") table (rather than a view as such) that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of extra storage and of some data being potentially out-of-date. Materialized views find use especially in data warehousing scenarios, where frequent queries of the actual base tables can be expensive.