2.8.1.3 Autopilot Indexing Examples

To evaluate all user defined schemas:

mysql> CALL sys.autopilot_index_advisor(NULL);

To evaluate selected schemas:

mysql> CALL  sys.autopilot_index_advisor(JSON_OBJECT('target_schema',JSON_ARRAY('schema1','schema2', ...)));

An example of the output that includes the following information:

• New indexes to create.

• Existing indexes to drop.

• The reason, expected storage footprint, and performance impact of each suggestion.

• The overall expected performance benefit and expected change to the storage footprint.

• An estimate of the time to create an index.

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| INDEX SUGGESTIONS                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Total Index suggestions: 5                                                                                                                                               |
| Statements analyzed:     519798                                                                                                                                          |
|                                                                                                                                                                          |
| SUGGEST           TABLE                                    INDEXED                                  REASON             ESTIMATED         ESTIMATED        ESTIMATED      |
| ACTION            NAME                                     COLUMNS                                  SUGGESTED          PERF IMPACT       FOOTPRINT        CREATE TIME    |
| -------           -----                                    -------                                  ---------          -----------       ---------        -----------    |
| CREATE            `world`.`city`                           `Population`                             Missing Index      HIGH              +   53.72 MiB          6 s      |
| CREATE            `world`.`state`                          `Area`, `Population`                     Missing Index      HIGH              +  116.79 MiB         13 s      |
| CREATE            `world`.`country`                        `Area`                                   Missing Index      LOW               +    1.27 MiB        134 ms     |
| CREATE            `world`.`continent`                      `ContinentCode`                          Missing Index      LOW               +   65.26 MiB          7 s      |
| DROP              `world`.`countrylanguage`                `CountryCode`                            Unused Index       --                -   64.00 KiB         --        |
|                                                                                                                                                                          |
| Expected performance benefit after applying all Index suggestions: 97.3%                                                                                                 |
| Expected storage footprint after applying all Index suggestions:  + 236.98 MiB                                                                                           |
|        64.00 KiB freed up by dropping indexes.                                                                                                                           |
|       237.05 MiB required for creating indexes.                                                                                                                          |
|       NOTE: Indexes will be stored efficiently at time of creation.                                                                                                      |
|             To accommodate efficient future inserts, size may double.                                                                                                    |
| Expected time for applying all Index creation suggestions:         25 s                                                                                                  |
|                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

To clear the statement digest in the Performance Schema and start collecting statements for a new workload:

mysql> CALL sys.ps_truncate_all_tables(FALSE);

See Section 2.8.1.5, “Autopilot Index Advisor Report Table”.