Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL? 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 



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