MySQL - Creating Materialized View

matview.sql

/*
   name:

   mv:  

   app: 
  
   note:
*/

-- ===================================================
-- create temp table
-- ===================================================
DROP TABLE IF EXISTS x;

CREATE TABLE x
SELECT PC.*, SF.*, PCS.*,
      -- Find the correct bottle for the date_crossed (the most recent one added on or before the date crossed)
      (select bottle_number
       from Labels_Common_FO lc
       where R.Barcode =lc.robot_id and Label_id='Bottles'
         and lc.date_added = ( select max(lc2.date_added)
                               from Labels_Common_FO lc2
                               where R.Barcode =lc2.robot_id
                                 and Label_id='Bottles'
                                 and lc2.date_added <= PC.date_crossed
                             ) limit 1
      ) as bottle_number
FROM Project_Crosses_FO PC
JOIN StockFinder_FO SF ON SF.__kp_UniqueID=PC._kf_Parent_UID
JOIN Reporter_FO R ON PC.reporter = R.Label_Name
OUTER JOIN project_crosses_suppl_FO PCS ON PCS.fk_project_crosses_id = PC.__kp_ProjectCrosses_Serial_Number
WHERE PC.project='Fly Olympiad'
 AND Wish_List IS NOT NULL
ORDER BY convert(wish_list,decimal),Reporter
;

CREATE INDEX x_kf_Parent_UID_ind ON x(kf_Parent_UID);

-- ===================================================
-- create materialized view
-- ===================================================
DROP TABLE IF EXISTS yy;
RENAME TABLE x TO yy;

-- ===================================================
-- create API view
-- ===================================================
CREATE OR REPLACE VIEW x_vw AS
SELECT *
FROM x;