11 Autopilot Index Advisor

Autopilot Index Advisor provides secondary index suggestions to improve the performance of OLTP workloads on DB Systems running MySQL 9.0.0 or later.

Indexes can improve query performance of SELECT statements, but managing the indexes increases the overhead of INSERT, UPDATE, and DELETE statements. The goal of Autopilot Index Advisor is to generate secondary index recommendations that optimize the overall workload performance.

Autopilot Index Advisor generates recommendations to add and drop indexes. Each recommendation also includes:

• An estimate of the overall workload performance benefit
• An estimate of the overall storage footprint impact
• A DDL statement, an explanation, and an estimated storage impact for each index recommendation
• An estimate of the index creation time and the estimated performance gain of the top five queries of each create index recommendation

The workload on the DB System is obtained from the statement digest history stored in the Performance Schema. The workload needs to have a minimum set of five queries that were executed against the tables in a user schema. Autopilot Index Advisor evaluates the statements and identifies potential indexes that can help improve the performance of the workload, as well as any existing indexes that are decreasing the performance.

• Running Autopilot Index Advisor
  

Related Topics

• Autopilot Indexing in the HeatWave User Guide