MySQL HeatWave User Guide
When Autopilot Indexing runs, it sends detailed output to
the autopilot_index_advisor_report table in
the sys schema. This includes execution
logs and generated load scripts.
The autopilot_index_advisor_report table is
a temporary table that contains data from the last execution
of Autopilot Indexing. Data is only available for the
current session and is lost when the session terminates or
when the server shuts down.
Query the autopilot_index_advisor_report
table after calls to Autopilot Indexing, as in the
following examples:
To view the DDL statements for the index suggestions:
mysql> SELECT log->>"$.sql" AS "SQL Script"
FROM sys.autopilot_index_advisor_report
WHERE type = "sql"
ORDER BY id;
+---------------------------------------------------------------------------------------+
| SQL Script |
+---------------------------------------------------------------------------------------+
| CREATE INDEX `autoidx_tab1108_col2` ON `world`.`city` ( `Population` ); |
| CREATE INDEX `autoidx_tab1110_col3_col2` ON `world`.`state` ( `Area`, `Population` ); |
| CREATE INDEX `autoidx_tab1104_col1` ON `world`.`country` ( `Area` ); |
| CREATE INDEX `autoidx_tab1109_col5` ON `world`.`continent` ( `ContinentCode` ); |
| DROP INDEX `countrylanguage_idx4` ON `world`.`countrylanguage` ( `CountryCode` ); |
+---------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
Then execute the script to implement the suggestions.
To view explanations for Autopilot Indexing recommendations:
mysql> SELECT JSON_PRETTY(log) AS "Explanations"
FROM sys.autopilot_index_advisor_report
WHERE type = "explain"
ORDER BY id;
+----------------------------------------------------------------------------------------------------+
| Explanations |
+----------------------------------------------------------------------------------------------------+
| {
"SQL": "CREATE INDEX `autoidx_tab1108_col2` ON `world`.`city` ( `Population` );",
"explanation": [
{
"reason": "Covering Index",
"query_text": "SELECT `Name` FROM `city` WHERE `Population` = ?",
"estimated_gain": "700.0x"
}
],
"est_create_time": "5.79 s"
} |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
The explanation includes the top 5 queries that benefit from the index, together with the reason and estimated gain of each query. It also includes the estimated time to create the index.
Learn how to Monitor MySQL HeatWave.