Use index advisor

MySQL   |  PostgreSQL   |  SQL Server

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Important: The index advisor is only available if you have enabled Gemini in Databases. For more information on enabling Gemini in Databases, see Set up Database Center.

This page describes the Cloud SQL for MySQL index advisor, and how you can view and apply its index recommendations.

Cloud SQL for MySQL offers an index advisor that tracks the queries your database handles. Periodically, it analyzes these queries to recommend new indexes that can increase the queries' performance.

You can view and query the index advisor's recommendations as a table, or request an on-demand analysis and report at any time.

Enable index advisor recommendations

To enable index advisor recommendations, add the cloudsql_index_advisor and performance_schema flags to your Cloud SQL for MySQL instance.

For more information on how to add a flag to your instance, see Configure database flags.

Disable index advisor recommendations

To disable index advisor recommendations, remove the cloudsql_index_advisor and performance_schema flags from your Cloud SQL for MySQL instance. For more information on how to remove a flag to your instance, see Configure database flags.

View the index advisor's recommendations

Cloud SQL for MySQL automatically runs the index advisor's analysis periodically.

You can read its results through the following table located in the mysql database:

• mysql.cloudsql_db_advisor_recommended_indexes: lists any recommended new indexes for each database. It also includes estimates of the storage required for each index, and the number of queries that each index can affect.

For example, to see the results of the most recent index-recommendation analysis, formatted as a table, run this query:

SELECT * FROM mysql.cloudsql_db_advisor_recommended_indexes;

Apply the index advisor's recommendations

The index column of the mysql.cloudsql_db_advisor_recommended_indexes table contains, in each row, a complete MySQL CREATE INDEX DDL statement for generating the index recommended in that row.

To apply that row's recommendation, run that DDL statement, exactly as presented.

For example, consider this output from manually running an analysis, using the query described in the previous section:

                    index                   | estimated_storage_size_in_mb
--------------------------------------------+------------------------------
 CREATE INDEX ON "School"."Students"("age") |                            3
(1 row)

This report contains a single recommendation: adding a single-column index on the age column in the School schema's Students table. To apply this advice, enter a DDL query as represented within the report:

CREATE INDEX ON "School"."Students"("age");

Configure the index advisor

While the index advisor is designed to work for most use cases with its default settings, you can fine-tune its behavior by setting various database flags.

By default, index advisor runs once every 24 hours. You can modify the autoschedule time using the cloudsql_index_advisor_auto_advisor_schedule flag, or run an ad hoc analysis on a specific date/time using the cloudsql_index_advisor_run_at_timestamp flag. For more information, see Index advisor flags.