2

What is the difference between PostgreSQL's materialized views and MySQL's views?

  • 3
    Why are you comparing PostgreSQL's materialised views to MySQL's views and not to PostgreSQL's views? – Andriy M Oct 20 '17 at 9:45
2

A VIEW is a stored query. When you select from it, you essentially run the query.

CREATE VIEW foo
AS
  SELECT col1,col2
  FROM bar;

Is essentially

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;

1

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: MERGE and TEMPTABLE. See 5.7 View Algorithms.

Think of MERGE as blending the VIEW definition with the query in question, then optimizing.

Think of 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.)

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.