11.1 Running Autopilot Index Advisor

To run Autopilot Index Advisor, the user must connect to the DB system and have the following MySQL privileges:

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

Perform the following steps to run Autopilot Index Advisor on the HeatWave Console:

1. Connect to the DB System. See Connecting from the Console.
2. In the HeatWave Console, click the Workspace tab, and then click the Autopilot Index Advisor tab. The Advisor runs on all the user schemas on the DB System.
3. View the index suggestions in the output.

   The suggestions provide the following information per index:

   • Index No.
   • Recommendation: It is either DROP or CREATE
   • Schema Name
   • Table Name
   • Indexed Columns
   • Reason: Reason for making the recommendation. Reasons for DROP include Unused Index and Duplicate Index, and reason for CREATE is Missing Index.
   • Performance Impact: This is not given for a DROP recommendation, and is HIGH or LOW for a CREATE recommendation.

   Select one or more of the suggestions by clicking on it. The bottom pane shows more information on the selected changes (click on the icon for each of the items for more information):

   • Estimated total speedup: A speedup estimate is provided only for CREATE recommendations, and only achievable if all selected CREATE recommendations are executed.
   • Total DB System data storage
   • Estimated storage impact
   • Selected recommendations
   • Performance/storage tradeoff plot: The storage impact for each recommendation and for all the recommendations together are plotted, with the performance impact indicated in the plot legend.

   Click the Show Affected Queries button to open the Queries most affected by the selected recommendations pane, which provides the following information for each of the queries (the information is listed in table format in the upper pane, and in more details in the lower pane when a query in the table is selected):

   • Query Text
   • Index ID
   • Reason for recommendation
   • Current execution time
   • Estimated speedup
4. Click the Refresh button to refresh the recommendations if you want, to get the latest recommendations according to the recent queries you ran on the DB System
5. Click the Apply Recommendations button to apply the recommendations you have selected. An Apply Selected Index Recommendations dialog box appears, in which you are asked to Confirm (or Cancel) the action.
   The recommendations for creating or dropping indexes are executed automatically.

   Tip:

   For DROP recommendations, you can make the indexes invisible first—if you encounter any errors later, you can make the indexes visible again; if there are no errors after some time, you can drop the indexes permanently.

   WARNING:

   If you drop any indexes that were used in the index hints FORCE INDEX, USE INDEX, or IGNORE INDEX for some queries, those queries will fail to execute.

Once the recommendations are applied, queries will start taking advantage of the changes for better performance. Repeat running the Autopilot Index Advisor to get new recommendation when, for example, your workload changes.

Autopilot Indexing in the HeatWave User Guide

Parent topic: Autopilot Index Advisor