For Auto Query Time Estimation to provide runtime data, a query history must be available. Auto Query Time Estimation can provide runtime data for up to 1000 queries, which is the MySQL HeatWave query history limit. To view the current MySQL HeatWave query history, query the performance_schema.rpd_query_stats table:

mysql> SELECT query_id, LEFT(query_text,160) 
          FROM performance_schema.rpd_query_stats;
*************************** 1. row ***************************
            query_id: 14
LEFT(query_text,160): SELECT country from airport_geo
*************************** 2. row ***************************
            query_id: 15
LEFT(query_text,160): CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country="UNITED STATES")
*************************** 3. row ***************************
            query_id: 16
LEFT(query_text,160): CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country="UNITED STATES")
...
...
9 rows in set (0.2190 sec)

The following example shows how to retrieve runtime data for the entire query history using Auto Query Time Estimation. In this example, there are three queries in the query history: a successfully executed query, a query that failed due to an out of memory error, and a query that was cancelled using Ctrl+C. For an explanation of Auto Query Time Estimation data, see Auto Query Time Estimation Data.

mysql> CALL sys.heatwave_advisor(JSON_OBJECT('query_insights', true));
+-------------------------------+
| INITIALIZING HEATWAVE ADVISOR |
+-------------------------------+
| Version: 4.31                 |
|                               |
| Output Mode: normal           |
| Excluded Queries: 0           |
| Target Schemas: All           |
|                               |
+-------------------------------+
6 rows in set (0.2276 sec)

+---------------------------------------------------------+
| ANALYZING LOADED DATA                                   |
+---------------------------------------------------------+
| Total 14 tables loaded in HeatWave for 1 schemas        |
| Tables excluded by user: 0 (within target schemas)      |
|                                                         |
| SCHEMA                            TABLES        COLUMNS |
| NAME                              LOADED         LOADED |
| ------                            ------         ------ |
| `airportdb`                           14            106 |
|                                                         |
+---------------------------------------------------------+
8 rows in set (0.2276 sec)

+--------------------------------------------------------------------------------------------------------------------+
| QUERY INSIGHTS                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------+
| Queries executed on Heatwave: 9                                                                                    |
| Session IDs (as filter): None                                                                                      |
|                                                                                                                    |
| QUERY-ID  SESSION-ID  QUERY-STRING                                                     EXEC-RUNTIME (s)  COMMENT   |
| --------  ----------  ------------                                                     ----------------  -------   |
|       14        5326  SELECT country from airport_geo                                      0.454                   |
|       15        5740  CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from...      0.576                   |
|       16        5748  CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from...      0.556                   |
|       17        5755  CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from...      0.614                   |
|       18        5916  CREATE TABLE flight_from_London SELECT * FROM flight WHERE `...      0.130                   |
|       19        5326  SELECT COUNTRY from airport_geo                                      0.003                   |
|       20        6396  CREATE TABLE flight_from_INDIA SELECT * FROM flight WHERE `f...      0.082                   |
|       21        6501  CREATE TABLE flight_from_SPAIN SELECT * FROM flight WHERE `f...      0.041                   |
|       22        6505  CREATE TABLE flight_from_NORWAY SELECT * FROM flight WHERE `...      0.045                   |
|                                                                                                                    |
| TOTAL ESTIMATED:   0   EXEC-RUNTIME:       0.000 sec                                                               |
| TOTAL EXECUTED:    9   EXEC-RUNTIME:       2.501 sec                                                               |
|                                                                                                                    |
|                                                                                                                    |
| Retrieve detailed query statistics using the query below:                                                          |
|     SELECT log FROM sys.heatwave_autopilot_report WHERE stage = "QUERY_INSIGHTS" AND type = "info";                |
|                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.2276 sec)
mysql> SELECT log FROM sys.heatwave_advisor_report
          WHERE stage = "QUERY_INSIGHTS"
          AND type = "info";
*************************** 1. row ***************************
log: {"comment": "", "query_id": 14, "query_text": "SELECT country from airport_geo", "session_id": 5326, "runtime_executed_ms": 453.5889625549317, "runtime_estimated_ms": 0.0}
*************************** 2. row ***************************
log: {"comment": "", "query_id": 15, "query_text": "CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country=\"UNITED STATES\")", "session_id": 5740, "runtime_executed_ms": 575.9282112121582, "runtime_estimated_ms": 2.279834}
*************************** 3. row ***************************
log: {"comment": "", "query_id": 16, "query_text": "CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country=\"UNITED STATES\")", "session_id": 5748, "runtime_executed_ms": 556.239128112793, "runtime_estimated_ms": 2.279834}
*************************** 4. row ***************************
log: {"comment": "", "query_id": 17, "query_text": "CREATE TABLE flight_from_US SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country=\"UNITED STATES\")", "session_id": 5755, "runtime_executed_ms": 614.0379905700684, "runtime_estimated_ms": 2.279834}
*************************** 5. row ***************************
log: {"comment": "", "query_id": 18, "query_text": "CREATE TABLE flight_from_London SELECT * FROM flight WHERE `from` IN (SELECT airport_id FROM airport_geo WHERE country=\"UNITED Kingdom\")", "session_id": 5916, "runtime_executed_ms": 130.10787963867188, "runtime_estimated_ms": 2.275197}
...
...
9 rows in set (0.2169 sec)

Auto Query Time Estimation provides the following data:

Auto Query Time Estimation data is available in machine readable format for use in scripts. Auto Query Time Estimation data is also available in JSON format or SQL table format with queries to the heatwave_autopilot_report table. See Section 5.8.8, “Autopilot Report Table”.

Learn more about Unload Advisor.