2.3.2 Running Queries

Before running a query, you can use EXPLAIN to determine if the query will be offloaded to HeatWave for processing. If so, the Extra column of EXPLAIN output shows: “Using secondary engine RAPID”.

mysql> EXPLAIN SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
          FROM orders
          WHERE O_ORDERDATE >= DATE '1994-03-01'
          GROUP BY O_ORDERPRIORITY
          ORDER BY O_ORDERPRIORITY;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 33.33
        Extra: Using where; Using temporary; Using filesort; Using secondary 
        engine RAPID

If Using secondary engine RAPID does not appear in the Extra column, the query will not be offloaded to HeatWave. To determine why a query will not offload, refer to Section 2.14, “Troubleshooting”, or try debugging the query using the procedure described in Section 2.3.5, “Debugging Queries”.

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

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
          FROM orders
          WHERE O_ORDERDATE >= DATE '1994-03-01'
          GROUP BY O_ORDERPRIORITY
          ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (0.04 sec)

To compare HeatWave query execution time with MySQL 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 MySQL DB System.

mysql> SET SESSION use_secondary_engine=OFF;

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
           FROM orders
           WHERE O_ORDERDATE >= DATE '1994-03-01'
           GROUP BY O_ORDERPRIORITY
           ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (8.91 sec)

Note

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