5.9.1 Autopilot Indexing

Autopilot Indexing obtains workloads from the statement digest history stored in the Performance Schema, evaluates them, and identifies potential indexes that can help improve workload performance.

Indexes can improve the query performance of SELECT statements, but index management can increase the overhead of DML operations, INSERT, UPDATE, and DELETE. Autopilot Indexing can generate secondary index recommendations that optimize the overall workload performance.

As of MySQL 9.0.0, MySQL HeatWave Autopilot Advisor includes Autopilot Indexing that can make secondary index suggestions to improve workload performance.

Autopilot Indexing generates recommendations to add and drop indexes and 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 5 queries of each create index recommendation.

To run Autopilot Indexing for OLTP workloads, the user must have the following MySQL privileges:

The PROCESS privilege.
The EXECUTE privilege on the sys schema.
The SELECT privilege on the Performance Schema.

To view Autopilot Indexing command-line help:

mysql> CALL sys.autopilot_index_advisor(JSON_OBJECT('output','help'));