1

Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN

statements

Can somebody clear this up for me? What's the "(materialized)" mean if materialized views aren't a thing in MySQL? Using MySQL Server 5.6, Workbench 6.3.

4

To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.

In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.

  • Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
  • Before 5.6, for multiple derived tables (for FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table.
  • 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)

There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)

5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.

  • Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables. – bjk116 Jan 9 at 16:49
  • 1
    @bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present a SELECT and SHOW CREATE TABLE (in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable. GROUP BY a ORDER BY b often requires two temp tables. But note: a temp table does not necessarily touch disk. – Rick James Jan 9 at 16:56
  • Thank you for the informative answer. You're right, I have a query with two "materialized" views and removing one of them and instead directly referencing tables made it much slower, and doing the same to the other showed no boost. However at least now I know. Thanks for the info! – bjk116 Jan 9 at 17:38
  • @bjk116 - 2 "materialized" in a single query probably happens with 2 "derived" tables: SELECT .. FROM ( SELECT ... ) AS m1 JOIN ( SELECT ... ) AS m2 ... -- It is best to try to turn at least one of those into a direct join at the outer layer. The materialized happens so that they can go to the extra effort of creating a useful index. That is indicated by <autokey> in the EXPLAIN. – Rick James May 6 at 16:34
0

Extended EXPLAIN Output Format

  • <materialize>(query fragment)

Subquery materialization is used.

  • Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database? – bjk116 Jan 9 at 16:13
  • Subquery != View – Akina Jan 9 at 16:28
  • A "materialized view" that is created permanently and maintained as changes are made to the underlying table. A non-materialized view is essentially syntactic sugar around a SELECT. Work is done every time you reference the view. Some cases work better with one; some with the other. – Rick James May 6 at 16:05

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.