5.4.1 Requirements for Running Queries

Ensure that the following requirements are met before offloading queries to MySQL HeatWave:

• Use a SELECT statement. INSERT ... SELECT and CREATE TABLE ... SELECT statements are supported,, but only the SELECT portion is offloaded to MySQL HeatWave.

  See Section 5.4.4, “CREATE TABLE ... SELECT Statements”, and Section 5.4.6, “INSERT ... SELECT Statements”.

• Define all tables accessed by the query using RAPID as the secondary engine.

• Load all accessed tables in MySQL HeatWave.

• Enable autocommit. If autocommit is disabled, MySQL HeatWave does not offload the query and execution is performed on the DB System. To check the autocommit setting:

  mysql> SHOW VARIABLES LIKE 'autocommit';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | autocommit    | ON    |
  +---------------+-------+

• To execute queries using the MySQL HeatWave secondary engine, set the use_secondary_engine system variable ,to either ON or FORCED state using one of the following syntax options. By default, this variable is set to ON.

  • ON: Queries are executed using the secondary engine only if supported by MySQL HeatWave (secondary_engine). If MySQL HeatWave execution is not possible for a query due to unsupported syntax or any other reason as mentioned in Diagnose Query Offload, the query is executed by the "DB System", and no errors are generated.

  • FORCED: Queries are executed exclusively using the secondary engine. If the query is not supported by MySQL HeatWave, it is not executed, and an error is be returned.

  mysql> SET SESSION use_secondary_engine = 'ON';
  Query OK, 0 rows affected (0.2831 sec)

  mysql> SET SESSION use_secondary_engine = 'FORCED';
  Query OK, 0 rows affected (0.3451 sec)

• Ensure that queries use only functions and operators supported by MySQL HeatWave. See Section 5.3, “Supported Functions and Operators”.

• Ensure that queries use only SQL modes supported by MySQL HeatWave.

• Queries should not include any known limitations.

If any prerequisite is not satisfied, the query is not offloaded and falls back to the DB System for processing by default.

What's Next

• Learn how to perform the following tasks:

  • Modify tables

  • Retrieve MySQL HeatWave Metadata

  • Optimize workloads for OLAP and OLTP

  • Monitor MySQL HeatWave processes