Use index advisor with Query Insights

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 how you can use the Cloud SQL for MySQL index advisor with Query Insights.

You can use the index advisor with Query Insights to view index advisor recommendations and create recommended indexes to improve query performance. To learn more about the index advisor, see Index advisor overview. To learn how to use the index advisor to view and query recommendations as a table, or to request an on-demand analysis and report, see Use the index advisor.

Before you begin

1. Set up Gemini.

2. To use query insights, you need specific IAM permissions. Create a custom role and add the cloudtrace.traces.get IAM permission to it. Then, add this role to each user account that needs to use Query insights.

3. To view query plans and their end-to-end views, your Google Cloud project must have the Trace API enabled. This setting lets your Google Cloud project receive trace data from authenticated sources at no additional cost. This data can help you detect and diagnose performance issues in your instance.

   To confirm that the Trace API is enabled, follow these steps:

   1. From the Google Cloud console, go to APIs and Services:

      Go to APIs and Services

   2. Click Enable APIs and Services.
   3. In the search bar, enter Trace API.
   4. If API enabled is displayed, then this API is enabled and there's nothing for you to do. Otherwise, click Enable.

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.

View and filter the index advisor recommendations

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Query Insights. The index advisor recommendations are displayed in the Recommendation column of the Top queries and tags section.
4. (Optional) To view only queries with CREATE INDEX recommendations, add a filter for Recommendation: Create Indexes.

View recommendations for a query

To view details of index recommendations for a specific query, follow these steps:

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Query Insights.
4. In the Top queries and tags section, click Queries.
5. To learn recommendation details about a query, do one of the following:
   • Click a query to learn more about recommendations for the selected query, including the following information:
     • Performance impact (high, medium, and low): The estimated query speed after all recommended indexes are created.
     • Recommendations: Create index recommendations.
     • Tables impacted: The number of tables that will be impacted when indexes are created.
     • Additional estimated storage needed: The estimated storage size needed to create all the recommended indexes.
     • Number of impacted queries: The total number of queries in the workload impacted by the index recommendations. An index may benefit multiple queries.
   • Click Create Indexes for a specific query to learn detailed recommendations about creating indexes to improve query performance.

View impacted queries

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Query Insights.
4. In the Top dimensions by database load table, click Queries.
5. Click Create indexes for a specific query.
6. Click Show impacted queries.
7. Click a query to learn details about the impacted query.

Create recommended indexes

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Query Insights.
4. In the Top dimensions by database load table, click Queries.
5. Click Create Indexes for a specific query.
6. Click Copy all index commands. The CREATE INDEX commands are copied to your clipboard.
7. Connect to the primary instance on the command line.

8. To create the recommended indexes, run the commands that were copied to your clipboard, for example:

   CREATE INDEX ON "public"."demo_order" ("customer_id");
   

What's next

• Learn how to use the index advisor to view and query recommendations as a table, or to request an on-demand analysis and report.
• Learn more about index advisor