2.5 Analyzing Tables

As of MySQL 9.3.0, you can use the ANALYZE TABLE statement to analyze tables loaded into HeatWave and update table statistics.

Before You Begin

• Ensure that the enable_secondary_engine_statistics system variables is set to ON.

• Check if you have the SELECT and INSERT privileges for the table that you want to analyze. If not, ask your admin user to grant you the required privileges.

Updating Table Statistics

Use the ANALYZE TABLE statement to update table statistics.

Press CTRL+C to copy
 
mysql>ANALYZE TABLE tbl_name;

For example:

Press CTRL+C to copy
 
mysql>ANALYZE TABLE airline;

The statement returns a table similar to the following:

Press CTRL+C to copy
 
+---------+--------------------------+----------+----------+
| Table   | Op                       | Msg_type | Msg_text |
+---------+--------------------------+----------+----------+
| airline | analyze                  | status   | OK       |
| airline | analyze secondary engine | status   | OK       |
+---------+--------------------------+----------+----------+

The details of columns available in this table are described in ANALYZE TABLE Output.

When you run the ANALYZE TABLE statement for a table loaded in HeatWave, the analysis is performed on HeatWave. The output contains an additional row showing the results of the analysis on the secondary engine.

ACE Statistics

The ANALYZE TABLE statement updates the Advanced Cardinality Estimation (ACE) statistics for tables loaded in HeatWave. ACE statistics models are a more powerful version of column histograms and are used by secondary engine to perform query optimization.

If the enable_secondary_engine_statistics system variable is set to ON, the secondary engine builds an ACE statistics model for a table when the ANALYZE TABLE statement is run on the table with or without the UPDATE HISTOGRAM clause.

The secondary engine does not build an ACE statistics model for a table in the following scenarios:

• The table does not support ACE statistics, or if it is an empty table.

• An ACE statistics model already exists for a table and no updates were made to the table since the last time the ACE statistics model was built.

When the secondary engine creates the ACE statistics model, the ACE_MODEL column is updated in the performance schema table rpd_tables.