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, set up Gemini in Databases.
Disable index advisor recommendations
To disable index advisor recommendations, remove the cloudsql_index_advisor
flag from your Cloud SQL for MySQL instance. If you enabled the
performance_schema flag for index advisor, then you can choose to
keep the performance_schema flag enabled or turn it off.
For more information about how to remove a flag from 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.