2.8.1.2 Running Autopilot Indexing

Autopilot Indexing provides recommendations for schemas with a representative workload that has at least five queries in the SQL statement digest history in the Performance Schema.

Autopilot Indexing only evaluates SQL statements in the SQL statement digest history that access existing tables. Autopilot Indexing does evaluate past SQL statements that access a table that has been dropped and recreated.

When the workload changes, invoke Autopilot Indexing again to update index recommendations.

Autopilot Indexing recommends indexes to create and drop. Rather than drop the suggested indexes, make them invisible first, and then drop them later after confirmation that there is no impact to any user queries. For instance, if a query uses an index hint, and that index is dropped, then the query will fail to execute. This includes FORCE INDEX, USE INDEX, and IGNORE INDEX, see Index Hints.

Autopilot Indexing does not evaluate the following:

• System schemas.

• Small InnoDB tables, with innodb_page_size less than 16KB, or less than 1,000 rows.

• Statements executed from stored routines and prepared statements.

• Columns with the following data types:

  • BINARY

  • VARBINARY

  • TINYBLOB

  • BLOB

  • MEDIUMBLOB

  • LONGBLOB

  • JSON

  • TEXT

  • TINYTEXT

  • MEDIUMTEXT

  • LONGTEXT

Autopilot Indexing does not make recommendations for the following:

• The creation of primary keys.

• Indexes that enforce foreign key constraints.

• Functional indexes.

• Indexes on partitioned tables.

• Indexes that speed up LIKE predicates.