2.8.1.5 Autopilot Index Advisor Report Table

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.

Index Advisor Report Table Query Examples

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.