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.