5.4.6 INSERT ... SELECT Statements

This topic describes how to run a SELECT query and insert the results into a target table using the INSERT ... SELECT statement. The target table can be an InnoDB table and does not need to use a secondary engine.

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

Before You Begin

• Review the requirements.

• Connect to your DB System.

• Verify if the query is offloaded to MySQL HeatWave for processing.

• The SELECT table is loaded in MySQL HeatWave, and the INSERT table is present on the DB System.

• The examples in this topic use the sample database airportdb. To learn how to download the sample database, see AirportDB Analytics Quickstart.

Execute INSERT ... SELECT Statement

When you execute an INSERT ... SELECT statement, the SELECT query is offloaded to MySQL HeatWave for execution, and result is copied into the target table in the DB System. This offloading reduces execution time, especially for long-running and complex queries. However, it may not be beneficial for SELECT queries that produce large result sets, as the large volume of DML operations on the DB System can limit performance improvements.

For example, the following statement inserts data into the flight_from_US table on the DB System, by selecting rows from the flight table on MySQL HeatWave. It filters the flights that depart from airports located in the United States, based on matching airport IDs present in table airport_geo.

mysql>  CREATE TABLE flight_from_US LIKE flight; 
Query OK, 0 rows affected (0.2368 sec) 

INSERT INTO flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country='UNITED STATES')
Query OK, 99431 rows affected (4.2551 sec)

Records: 99431  Duplicates: 0  Warnings: 0

EXPLAIN INSERT INTO flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country='UNITED STATES');
*************************** 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.2304 sec)

Note the following:

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

• The ON DUPLICATE KEY UPDATE clause is not supported.

• INSERT INTO some_view SELECT statements are not offloaded. Setting use_secondary_engine=FORCED does not cause the statement to fail with an error. The statement is executed on the DB System regardless of the use_secondary_engine setting.

• As of MySQL 9.2.0, MySQL HeatWave can offload the SELECT query in an INSERT ... SELECT statement, even if the target table has a trigger.

  In MySQL versions prior to 9.2.0, MySQL HeatWave cannot offload the query when the target table has a trigger.. If you set use_secondary_engine=FORCED, the operation fails and generates an offload error.

• See Section 5.4.1, “Requirements for Running Queries” and Section 11.2.1.9, “Other Limitations”.

What's Next

Learn how to query views.