5.4.8 Query Materalized Views

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.

Automated Substitution of Materialized Views

As of MySQL 9.6.0, MySQL HeatWave automatically identifies if a subtree of a query can be substituted using an exising materialized view that you created earlier.

Consider the following example:

Create a materialized view.

mysql> CREATE MATERIALIZED VIEW LINEITEM_PART1 
AS SELECT * 
FROM LINEITEM, PART 
WHERE L_PARTKEY = P_PARTKEY AND P_BRAND = "Brand#23";

Run a query referencing the materialized view.
```
mysql> SELECT COUNT(*) FROM LINEITEM_PART1;
```
Run a query that contains a subtree that can be from the LINEITEM_PART1 materialized view.
```
mysql> SELECT * FROM LINEITEM, ORDERS, PART 
WHERE L_ORDERKEY = O_ORDERKEY 
AND L_PARTKEY = P_PARTKEY 
AND P_BRAND = ”Brand#23” 
AND O_ORDERSTATUS = ”A”;
```
MySQL HeatWave analyzes the query and makes appropriate substitutions to the query automatically. The following query shows the automated substitution. You are not required to run this query.
```
SELECT * FROM ORDERS, LINEITEM_PART1 
WHERE O_ORDERKEY = L_ORDERKEY 
AND O_ORDERSTATUS = ‘A’;
```

Query the rpd_query_stats table to confirm if automated substitution occurred.

mysql> SELECT 
JSON_EXTRACT(QKRN_TEXT,'$.qkrnNodesArray[*].nodeSubType', '$.qkrnNodesArray[*].tableId') 
FROM performance_schema.rpd_query_stats 
ORDER BY MYSQL_QUERY_ID DESC 
LIMIT 1;

Confirm that nodeSubType appears as Materialized Table.

Only up-to-date materialized views are considered for automated substitution. A materialized view is up-to-date (fresh) when all base tables that it references have not undergone any changes since the materialized view's last materialization. If any base table undergoes a change, the materialized view is deemed out-of-date (stale). It is re-materialized when a query references it directly in the future.

You can refresh materialized views by running any query that references them. For example:

mysql> SELECT COUNT(*) FROM LINEITEM_PART1;

rapid_auto_create_materialized_view Variable

MySQL 9.6.0 introduces the rapid_auto_create_materialized_view variable. When enabled, the MySQL HeatWave optimizer tries to automatically create materialized views, both from final or intermediate results, from queries that are run in the system. These automatically created materialized views act like a result cache for future queries.

To enable this feature, set the variable by running the following command:

mysql> SET SESSION rapid_auto_create_materialized_view=1;

You can view automatically created materialized views in the performance_schema.rpd_tables and performance_schema.rpd_table_id tables.

To confirm the usage of materialized views after running a query, you can run the following command:

mysql> SELECT 
JSON_EXTRACT(QKRN_TEXT,'$.qkrnNodesArray[*].nodeSubType', '$.qkrnNodesArray[*].tableId') 
FROM performance_schema.rpd_query_stats 
ORDER BY MYSQL_QUERY_ID DESC 
LIMIT 1;

To disable rapid_auto_create_materialized_view, and delete all automatically created materialized views, run the following command:

mysql> SET SESSION rapid_auto_create_materialized_view=0;

What's Next

Learn how to View Query Runtimes and Estimates.