5.8 Optimize Workloads for OLAP

Workload optimization for online analytical processing, OLAP, includes using dictionary encoding for certain string columns and defining data placement keys to optimize for JOIN and GROUP BY query performance. Apply these optimizations manually, or use MySQL HeatWave Autopilot Advisor, which includes the following:

Auto Encoding recommends string column encodings that minimize the required cluster size and improve query performance.
Auto Data Placement recommends data placement keys that optimize JOIN and GROUP BY query performance.
Auto Query Time Estimation provides runtime information for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled with Ctrl+C, and queries that fail due to out of memory errors. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.
Unload Advisor
Recommends tables to unload, reducing MySQL HeatWave memory usage. The recommendations are based upon when the tables were last queried.

Advisor is workload-aware and provides recommendations based on machine learning models, data analysis, and MySQL HeatWave query history. Advisor analyzes the last 1000 successfully executed MySQL HeatWave queries.

Advisor is implemented as a stored procedure named heatwave_advisor, which resides in the MySQL sys schema.

To run Advisor for OLAP workloads, the MySQL HeatWave Cluster must be active, and the user must have the following MySQL privileges:

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