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

+---------------------------------------------------------------------------------------------------------+
| INDEX SUGGESTIONS                                                                                       |
+---------------------------------------------------------------------------------------------------------+
| Total Index suggestions: 2                                                                              |
| Statements analyzed:     5323                                                                           |
|                                                                                                         |
| SUGGEST    TABLE                        INDEXED          REASON             ESTIMATED    ESTIMATED      |
| ACTION     NAME                         COLUMNS          SUGGESTED          FOOTPRINT    PERF IMPACT    |
| -------    -----                        -------          ---------          ---------    -----------    |
| CREATE     `world`.`city`               `Population`     Missing Index    68.96 KiB +    LOW            |
| DROP       `world`.`countrylanguage`    `CountryCode`    Unused Index     64.00 KiB -    --             |
|                                                                                                         |
| Expected performance benefit after applying all Index suggestions: 1.4%                                 |
| Expected storage footprint after applying all Index suggestions:  + 4.96 KiB                            |
|        64.00 KiB freed up by dropping indexes.                                                          |
|        68.96 KiB required for creating indexes.                                                         |
|       NOTE: Indexes will be stored efficiently at time of creation.                                     |
|             To accommodate efficient future inserts, size may double.                                   |
|                                                                                                         |
+---------------------------------------------------------------------------------------------------------+

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”.