5.4.5 Create MySQL HeatWave Temporary Tables

As of MySQL 9.3.0, you can create temporary tables that are stored in the MySQL HeatWave Cluster. A temporary table is visible only within the current session, and is dropped automatically when you close the session.

Once created, you can use a MySQL HeatWave temporary table as an independent entity. After the MySQL HeatWave temporary table is created, you can unload the table that you used to create the temporary table. If you make updates in the original table after the temporary table is created, the updates are not reflected in the temporary table.

Unlike the MySQL temporary table that exists in DB System, MySQL HeatWave temporary tables are stored in the MySQL HeatWave Cluster by the MySQL HeatWave engine and are used for MySQL HeatWave processing.

Refer to the following sections, to create a MySQL HeatWave temporary table and execute queries.

Before You Begin

To create a MySQL HeatWave temporary table, you must have the CREATE TEMPORARY TABLES privilege. After a session has created a temporary table, the server performs no further privilege checks on the table.
Load the original table based on which you want to create a temporary table in the MySQL HeatWave Cluster.
The examples in this topic use the sample database airportdb. To learn how to download the sample database, see AirportDB Analytics Quickstart.

Create a MySQL HeatWave Temporary Table

As of MySQL 9.4.0, to create a MySQL HeatWave temporary table, use the CREATE TEMPORARY TABLE statement with the ENGINE=RAPID option:
```
mysql> CREATE TEMPORARY TABLE tbl_name 
ENGINE=RAPID 
SELECT …;
```
For example:
```
mysql> CREATE TEMPORARY TABLE seat_sold_htt
ENGINE=RAPID
AS
SELECT flight_id, COUNT(*) AS sold_seat
FROM booking
GROUP BY flight_id;
```
For versions earlier than MySQL 9.4.0, use the CREATE TEMPORARY TABLE statement with the SECONDARY_ENGINE=RAPID option instead of the ENGINE=RAPID option:
```
mysql> CREATE TEMPORARY TABLE tbl_name 
SECONDARY_ENGINE=RAPID
SELECT …;
```
Alternatively, as of MySQL 9.4.0, you can set the default_tmp_storage_engine variable to RAPID to specify the primary engine for MySQL HeatWave temporary tables for the current session:
```
mysql> SET SESSION default_tmp_storage_engine='RAPID';
```
In this case, use the CREATE TEMPORARY TABLE statement without the ENGINE=RAPID option to create a MySQL HeatWave temporary table:
```
mysql> CREATE TEMPORARY TABLE tbl_name 
SELECT …;
```
For example:
```
mysql> CREATE TEMPORARY TABLE seat_sold_htt
AS
SELECT flight_id, COUNT(*) AS sold_seat
FROM booking
GROUP BY flight_id;
```

The temporary table information is added to the rpd_tables performance schema table, and a new column MATERIALIZATION_QUERY is added to the rpd_table_id performance schema table. The information related to the query used to create the temporary table, is provided in the MATERIALIZATION_QUERY column.

mysql> SELECT TABLE_NAME, MATERIALIZATION_QUERY FROM performance_schema.rpd_table_id\G
*************************** 1. row ***************************
           TABLE_NAME: seat_sold_htt
MATERIALIZATION_QUERY: CREATE TEMPORARY TABLE seat_sold_htt
ENGINE=RAPID
AS
SELECT flight_id, COUNT(*) AS sold_seat
FROM booking
GROUP BY flight_id

Comparing Query Execution Times

The following examples demonstrate how to calculate load factor of each airline for which data is available in the airportdb database, using a derived table and a MySQL HeatWave temporary table.

Run the following query to calculate the load factor using a derived table seat_sold.

mysql> SELECT airlinename 'Airline Name', SUM(sold_seat)/SUM(capacity) 'Load Factor'
FROM (SELECT flight_id, COUNT(*) sold_seat FROM booking GROUP BY flight_id) seat_sold 
  JOIN flight USING (flight_id) 
  JOIN airplane USING (airplane_id) 
  JOIN airline ON airline.airline_id = flight.airline_id 
GROUP BY airlinename 
ORDER BY airlinename;
+----------------------+-------------+
| Airline Name         | Load Factor |
+----------------------+-------------+
| Afghanistan Airlines |      0.4925 |
| Albania Airlines     |      0.4927 |
| American Samoa Airli |      0.4936 |
| Angola Airlines      |      0.4926 |
| Argentina Airlines   |      0.4922 |
| Australia Airlines   |      0.4921 |
| Azerbaijan Airlines  |      0.4930 |
| Bahamas Airlines     |      0.4927 |
|...                   |             |
| Zimbabwe Airlines    |      0.4934 |
+----------------------+-------------+
113 rows in set (4.8334 sec)

Run the following query to calculate the load factor using MySQL HeatWave temporary table seat_sold_htt created in Creating a MySQL HeatWave Temporary Table section.

mysql> SELECT airlinename 'Airline Name', SUM(sold_seat)/SUM(capacity) 'Load Factor'
FROM seat_sold_htt 
  JOIN flight USING (flight_id) 
  JOIN airplane USING (airplane_id) 
  JOIN airline ON airline.airline_id = flight.airline_id 
GROUP BY airlinename 
ORDER BY airlinename;
+----------------------+-------------+
| Airline Name         | Load Factor |
+----------------------+-------------+
| Afghanistan Airlines |      0.4925 |
| Albania Airlines     |      0.4927 |
| American Samoa Airli |      0.4936 |
| Angola Airlines      |      0.4926 |
| Argentina Airlines   |      0.4922 |
| Australia Airlines   |      0.4921 |
| Azerbaijan Airlines  |      0.4930 |
| Bahamas Airlines     |      0.4927 |
|...                   |             |
| Zimbabwe Airlines    |      0.4934 |
+----------------------+-------------+
113 rows in set (0.3585 sec)

Creating the MySQL HeatWave temporary table takes approximately 4 seconds. However, it significantly reduces query execution time—from 4.8 seconds to just 0.35 seconds. Reusing the temporary table across multiple reports balances the initial creation cost.

What's Next

Learn how to use the INSERT ... SELECT Statements.