7.2.4 Running AirportDB Queries

After airportdb sample database tables are loaded into the HeatWave Cluster, queries that qualify are automatically offloaded to the HeatWave Cluster for accelerated processing. To run queries:

Note

If you are using MySQL HeatWave on AWS, you can also run queries from the Query Editor in the MySQL HeatWave Console. See Running Queries, in the MySQL HeatWave on AWS Service Guide.

1. Start MySQL Shell and connect to the MySQL DB System endpoint:

   $> mysqlsh Username@DBSystem_IP_Address_or_Host_Name

   MySQL Shell opens in JavaScript execution mode by default.

   MySQL>JS>

2. Change the MySQL Shell execution mode to SQL:

   MySQL>JS> \sql

3. Change to the airportdb database.

   MySQL>SQL> USE airportdb;
   Default schema set to `airportdb`.Fetching table and column names from `airportdb` for 
   auto-completion... Press ^C to stop.

4. Before running a query, use EXPLAIN to verify that the query can be offloaded to the HeatWave Cluster. For example:

   MySQL>SQL> EXPLAIN SELECT booking.price, count(*)
                    FROM booking
                    WHERE booking.price > 500
                    GROUP BY booking.price
                    ORDER BY booking.price LIMIT
                    10;
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: booking
      partitions: NULL
            type: ALL
   possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 54081693
        filtered: 33.32999801635742
           Extra: Using where; Using temporary; Using filesort; Using secondary engine RAPID

   If the query can be offloaded, the Extra column in the EXPLAIN output reports Using secondary engine RAPID.

5. After verifying that the query can be offloaded, run the query and note the execution time.

   MySQL>SQL> SELECT booking.price, count(*) 
                    FROM booking 
                    WHERE booking.price > 500
                    GROUP BY booking.price 
                    ORDER BY booking.price 
                    LIMIT 10;
   +--------+----------+
   | price  | count(*) |
   +--------+----------+
   | 500.01 |      860 |
   | 500.02 |     1207 |
   | 500.03 |     1135 |
   | 500.04 |     1010 |
   | 500.05 |     1016 |
   | 500.06 |     1039 |
   | 500.07 |     1002 |
   | 500.08 |     1095 |
   | 500.09 |     1117 |
   | 500.10 |     1106 |
   +--------+----------+
   10 rows in set (0.0537 sec)

6. To compare the HeatWave execution time with MySQL DB System execution time, disable the use_secondary_engine variable to see how long it takes to run the same query on the MySQL DB System; for example:

   MySQL>SQL> SET SESSION use_secondary_engine=OFF;
   MySQL>SQL> SELECT booking.price, count(*) 
                    FROM booking 
                    WHERE booking.price > 500
                    GROUP BY booking.price 
                    ORDER BY booking.price 
                    LIMIT 10;
   +--------+----------+
   | price  | count(*) |
   +--------+----------+
   | 500.01 |      860 |
   | 500.02 |     1207 |
   | 500.03 |     1135 |
   | 500.04 |     1010 |
   | 500.05 |     1016 |
   | 500.06 |     1039 |
   | 500.07 |     1002 |
   | 500.08 |     1095 |
   | 500.09 |     1117 |
   | 500.10 |     1106 |
   +--------+----------+
   10 rows in set (9.3859 sec)

For other airportdb sample database queries that you can run, see Section 7.2.5, “Additional AirportDB Queries”. For more information about running queries, see Section 2.3, “Running Queries”.