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.
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.
-
As of MySQL 9.4.0, to create a MySQL HeatWave temporary table, use the
CREATE TEMPORARY TABLE
statement with theENGINE=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 theSECONDARY_ENGINE=RAPID
option instead of theENGINE=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 toRAPID
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 theENGINE=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
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.
Learn how to use the INSERT ... SELECT Statements.