MySQL HeatWave User Guide  /  ...  /  Running tpch Queries

7.1.5 Running tpch Queries

This topic describes how to query tpch data in the HeatWave Cluster. After 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 tpch database:

    MySQL>SQL> USE tpch;
    Default schema set to `tpch`.Fetching table and column names from `tpch` 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 SUM(l_extendedprice * l_discount) AS revenue
                     FROM lineitem 
                     WHERE l_shipdate >= date '1994-01-01';
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: lineitem
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 56834662
         filtered: 33.33
            Extra: Using where; 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 SUM(l_extendedprice * l_discount) AS revenue
                     FROM lineitem
                     WHERE l_shipdate >= date '1994-01-01';
    +------------------+
    | revenue          |
    +------------------+
    | 82752894454.9036 |
    +------------------+
    1 row in set (0.04 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 SUM(l_extendedprice * l_discount) AS revenue
                     FROM lineitem
                     WHERE l_shipdate >= date '1994-01-01';
    +------------------+
    | revenue          |
    +------------------+
    | 82752894454.9036 |
    +------------------+
    1 row in set (24.20 sec)

    For other tpch sample database queries that you can run, see Section 7.1.6, “Additional tpch Queries”. For more information about running queries, refer to Section 2.3, “Running Queries”.