Mysql – Why are some views listed as Materialized in MySQL explain statements if Materialized views don’t exist in MySQL

materialized-viewMySQLmysql-5.6

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.

Best Answer

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.

Related Solutions

Mysql – Complex MySQL Query returning almost all the correct rows

Something like

SELECT DISTINCT 
o.orderid,
i.invoiceid,
i.subinvoicenumber,
NULL AS oistockingorderid,
po.purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o 
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    JOIN tblpurchaseorders po 
      ON o.orderid=po.orderid 
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

UNION

SELECT DISTINCT 
o.orderid,
i.invoiceid,
i.subinvoicenumber,
stockingorderid AS oistockingorderid,
NULL AS purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o 
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN tblpurchaseorders po 
      ON o.orderid=po.orderid 
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

The second SELECT is just the same as your original. In the calculated fields you can omit the COALESCE from grosscost, and use the two halves separately (according to the non-NULL ID).

Mysql – Select row from one query where opposites are not present in second query

SELECT  m.id
    FROM  message AS m
    WHERE  NOT EXISTS 
      ( SELECT  *
            FROM  inbox AS i
            WHERE  i.message_id = m.id
              AND  i.to_user_id = m.from_user_id 
      )

I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.

Best Answer

Related Solutions

Mysql – Complex MySQL Query returning almost all the correct rows

Mysql – Select row from one query where opposites are not present in second query

Related Question