5.4.3 Explain Query and Check Execution Time

This topic describes how to use the EXPLAIN statement to analyze query execution and verify whether a query is offloaded to MySQL HeatWave for accelerated processing.

Before You Begin

• Review the requirements.

• Connect to your 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 EXPLAIN Statement

Before running a query, you can use EXPLAIN to determine if the query is offloaded to MySQL HeatWave for processing. If so, the Extra column of EXPLAIN output includes Using secondary engine RAPID.

If Using secondary engine RAPID does not appear in the Extra column, the query is not offloaded to MySQL HeatWave. To determine why a query is not offload, refer to Diagnose Query Offload Errors or MySQL HeatWave Issues to diagnose and resolve the offloading error.

Press CTRL+C to copy
 
mysql> EXPLAIN SELECT airline_id, 
          COUNT(*) AS flight_id FROM flight 
          WHERE flight_id <= 66810 
          GROUP BY airline_id 
          ORDER BY airline_id;

*************************** 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.2521 sec)

After using EXPLAIN to verify that the query can be offloaded, run the query and note the execution time.

Press CTRL+C to copy
 
mysql> SELECT airline_id, COUNT(*) AS flight_id
          FROM flight
          WHERE flight_id <= 66810
          GROUP BY airline_id
          ORDER BY airline_id;
+------------+-----------+
| airline_id | flight_id |
+------------+-----------+
|         1 |        436 |
|         2 |        430 | 
|...        |            |
+------------+-----------+
113 rows in set (0.2953 sec)

To compare MySQL HeatWave query execution time with DB System execution time, disable the use_secondary_engine variable and run the query again to see how long it takes to run on the DB System.

Press CTRL+C to copy
 
mysql> SET SESSION use_secondary_engine=OFF;

mysql> SELECT airline_id, COUNT(*) AS flight_id
          FROM flight
          WHERE flight_id <= 66810
          GROUP BY airline_id
          ORDER BY airline_id;
+------------+-----------+
| airline_id | flight_id |
+------------+-----------+
|         1 |        436 |
|         2 |        430 | 
|...        |            |
+------------+-----------+
113 rows in set (0.4476 sec)

Note

Concurrently issued queries are prioritized for execution. For information about query prioritization, see Section 5.4.11, “Auto Scheduling”.

What's Next

Learn how to use Create Table ... SELECT Statements.