MySQL :: HeatWave User Guide :: 5.4.8 Query Materalized Views

HeatWave User Guide / ... / Query Materalized Views

5.4.8 Query Materalized Views

This topic describes how to create materialized views on MySQL HeatWave, which is supported as of MySQL 9.5.0.

A materialized view is a database object that stores the results of a query, rather than computing the result dynamically each time the view is accessed.

Before You Begin

Review the requirements.
Connect to your DB System.
Verify if the query is offloaded to MySQL HeatWave for processing.
The examples in this topic use the sample database airportdb. To learn how to download the sample database, see AirportDB Analytics Quickstart.

Create and Query a Materialized View

To create or alter a materialized view, you use the CREATE VIEW or ALTER VIEW statements.

The following example creates a materialized view for data in the in the airportdb database, which is described in the AirportDB Analytics Quickstart. The example assumes all tables are loaded in MySQL HeatWave.

mysql> CREATE MATERIALIZED VIEW airport_US
AS
SELECT
    airport.*
FROM
    airport
    JOIN airport_geo ON airport.airport_id = airport_geo.airport_id
WHERE
    airport_geo.country = 'UNITED STATES';

The materialized view retrieves information on U.S.-based airports by joining the airport_id value in the airport and airport_geo tables.

Materialization does not occur until you run a query using the materialized view.

To verify the materialized view was successfully created, you can query a sample of the data from the materialized view (airport_US), which triggers materialization. See the following example:

mysql> SELECT * FROM airportdb.airport_US limit 10;
+------------+------+------+----------------------------+
| airport_id | iata | icao | name                       |
+------------+------+------+----------------------------+
|        263 | AGC  | KAGC | ALLEGHENY CO               |
|       1497 | NULL | KOEB | BRANCH CO MEML             |
|       2850 | NULL | KCUT | CUSTER CO                  |
|       3737 | SKA  | KSKA | FAIRCHILD AFB              |
|       3774 | NULL | KHZR | FALSE RIVER REGIONAL       |
|       3829 | FFM  | KFFM | FERGUS FALLS MUN-MICKELSON |
|       5759 | JDN  | KJDN | JORDAN                     |
|       6662 | LFI  | KLFI | LANGLEY AFB                |
|       7125 | PRC  | KPRC | LOVE                       |
|       8778 | VGT  | KVGT | NORTH LAS VEGAS            |
+------------+------+------+----------------------------+
10 rows in set (0.0511 sec)

You can view information from the materialized view by querying the performance_schema database.

The following example queries the rpd_table_id table for queries with the airport_US materialized view. The ID value of the query (165) is then used to query the rpd_tables table.

mysql> SELECT * FROM performance_schema.rpd_table_id WHERE NAME='airportdb.airport_US'\G
************************** 1. row ***************************
                   ID: 165
                 NAME: airportdb.airport_US
          SCHEMA_NAME: airportdb
           TABLE_NAME: airport_US2
MATERIALIZATION_QUERY: select `airportdb`.`airport`.`airport_id` AS `airport_id`,`airportdb`.`airport`.`iata` AS `iata`,`airportdb`.`airport`.`icao` AS `icao`,
                       `airportdb`.`airport`.`name` AS `name` from (`airportdb`.`airport` join `airportdb`.`airport_geo` on((`airportdb`.`airport`.`airport_id` = `airportdb`.`airport_geo`.`airport_id`))) 
                       where (`airportdb`.`airport_geo`.`country` = 'UNITED STATES')
1 row in set (0.0407 sec)
 mysql> SELECT * FROM performance_schema.rpd_tables WHERE ID='165'\G
 ************************** 1. row ***************************
                         ID: 165
               SNAPSHOT_SCN: 257
              PERSISTED_SCN: 1
                  POOL_TYPE: VOLATILE
        DATA_PLACEMENT_TYPE: RoundRobin
                      NROWS: 2229
                LOAD_STATUS: AVAIL_RPDGSTABSTATE
              LOAD_PROGRESS: 100
                 SIZE_BYTES: 46215
       TRANSFORMATION_BYTES: NULL
                QUERY_COUNT: 2
               LAST_QUERIED: 2025-10-06 17:37:20.015999
       LOAD_START_TIMESTAMP: NULL
         LOAD_END_TIMESTAMP: 2025-10-06 17:23:38.980762
            RECOVERY_SOURCE: NULL
   RECOVERY_START_TIMESTAMP: NULL
     RECOVERY_END_TIMESTAMP: NULL
                  LOAD_TYPE: USER
LOGICAL_PARTS_LOADED_AT_SCN: "NOT_PARTITIONED"
           AUTO_ZMP_COLUMNS: "NO COLUMNS"
                  ACE_MODEL: NOT AVAILABLE
1 row in set (0.0491 sec)

The rpd_table_id table displays query information for the materialized view in the MATERIALIZATION QUERY column.

The rpd_tables table shows that the materialized view is in the appropriate pool type for materialized views, VOLATILE.

To remove the materialized view, use the DROP VIEW statement.

Note the following regarding materialized views:

If there are any changes to the base tables referenced by a materialized view, MySQL HeatWave re-materializes the view when a query is issued that references the materialized view. This ensures the view displays up-to-date data.
If a materialized view refers directly to another materialized view, the referred materialized view is treated as a normal view. This means materialized views can only refer to base tables directly, and not other materialized views.
You cannot directly update a materialized view, so INSERT, UPDATE, and DELETE statements do not work with materialized views.

What's Next

Learn how to View Query Runtimes and Estimates.

PREV HOME UP NEXT