MySQL HeatWave User Guide

5.4.4 CREATE TABLE ... SELECT Statements

This topic describes how to create a new table and insert data using a SELECT query.

When the query is offloaded to MySQL HeatWave, it is processed faster, improving the overall performance of the operation.

Note

However, the CREATE TABLE ... SELECT statement is not permitted when the sql_require_primary_key system variable is enabled. This setting is enabled by default in all high availability database configurations.

Before You Begin

Execute Create TABLE ... SELECT Statement

When a CREATE TABLE ... SELECT statement is executed, the SELECT query is offloaded to MySQL HeatWave for processing, while the table is created in the DB System. This offloading speeds up the execution of long-running and complex queries. However, if the SELECT query returns a large result set, queries that produce large result sets performance may be limited because inserting the data into the DB System takes time.

For example, the following statement sample uses the SELECT statement with a subquery to find all flights where the departure airport (from) is located in the United Kingdom. Then, using the CREATE TABLE ... SELECT it creates a new table called flight_from_UK. 

mysql> EXPLAIN SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country='UNITED KINGDOM');

*************************** 1. row ***************************
           id: 1
  select_type: NONE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using secondary engine RAPID. Use EXPLAIN FORMAT=TREE to show the plan.
1 row in set, 1 warning (0.2239 sec)

mysql> CREATE TABLE flight_from_UK SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country='UNITED KINGDOM');
Query OK, 10777 rows affected (0.3180 sec)

Records: 10777  Duplicates: 0  Warnings: 0

The SELECT portion of the CREATE TABLE ... SELECT statement is subject to the same MySQL HeatWave requirements and limitations as regular SELECT queries.

What's Next

Learn how to create MySQL HeatWave temporary tables.

MySQL Documentation Library